oracle 分区基自动扩展,使用Oracle 11g分区自动扩展的功能的一个小问题

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 [email protected]  file=d:/CALL_LOG.dmp  tables=SOA_CALL_LOG

C:\Users\Administrator>exp [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

)

);

原文：http://blog.csdn.net/stevendbaguo/article/details/46520359