使用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_LOGC:\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 ) );
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。