使用Oracle11g分區自動擴展的功能的一個小問題

使用Oracle11g分區自動擴展的功能的一個小問題

Oracle 11g下使用分區自動擴展的功能，非常方便，不過同時也帶來一個問題，就是導出、導入之後，建表語句改了，下面來做個實驗：

SQL> select * from v$version;
BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1. 初始化數據

drop table CALL_LOG purge;

create table CALL_LOG
(
LOG_ID NUMBER(10) not null,
INVOKE_TIME TIMESTAMP(6)
)
partition by range (INVOKE_TIME) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(
partition CALL_LOG_P_2015_03_01 values less than (to_date('2015_03_01', 'yyyy-mm-dd'))
);
insert into CALL_LOG(LOG_ID,INVOKE_TIME) values(4,sysdate -4);
insert into CALL_LOG(LOG_ID,INVOKE_TIME) values(5,sysdate -3);
insert into CALL_LOG(LOG_ID,INVOKE_TIME) values(6,sysdate -2);
insert into CALL_LOG(LOG_ID,INVOKE_TIME) values(7,sysdate -1);
commit;

2.導出、導入數據

不能用exp，因為這個是新特性，exp不支持

exp test/[email protected] file=d:/CALL_LOG.dmp tables=SOA_CALL_LOG
C:\Users\Administrator>exp test/[email protected] file=d:/CALL_LOG.dmp tables=SOA_CALL_LOG
Export: Release 11.2.0.1.0 - Production on 星期二 6月 16 16:49:27 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
連接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已導出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服務器使用 AL32UTF8 字符集 (可能的字符集轉換)


即將導出指定的表通過常規路徑...
EXP-00006: 出現內部不一致的錯誤
EXP-00000: 導出終止失敗

expdp test/test directory=DATA_PUMP_DIR dumpfile=CALL_LOG.dmp tables=CALL_LOG
impdp test/test directory=DATA_PUMP_DIR dumpfile=CALL_LOG.dmp

3.導入後看下表的定義，變成固定的了

create table CALL_LOG
(
LOG_ID NUMBER(10) not null,
INVOKE_TIME TIMESTAMP(6)
)
partition by range (INVOKE_TIME)
(
partition CALL_LOG_P_2015_03_01 values less than (TIMESTAMP' 2015-03-01 00:00:00')
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P28 values less than (TIMESTAMP' 2015-06-13 00:00:00')
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P29 values less than (TIMESTAMP' 2015-06-14 00:00:00')
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P30 values less than (TIMESTAMP' 2015-06-15 00:00:00')
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P31 values less than (TIMESTAMP' 2015-06-16 00:00:00')
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
);