使用Oracle 11g分區自動擴充的功能的一個小問題，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/test@10.10.15.150 file=d:/CALL_LOG.dmp tables=SOA_CALL_LOG
C:\Users\Administrator>exp test/test@10.10.15.150 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
)
);
