Oracle 11g under the function of automatic expansion of the partition, it is very convenient, but also brings a problem, that is, after the export, import, the build Table statement changed, the following to do an experiment:
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. Initializing data
drop table Call_log Purge;
CREATE TABLE Call_log
(
LOG_ID Number (TEN) is 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. Exporting and importing data
Exp cannot be used because this is a new feature and EXP does not support
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 Tuesday June 16 16:49:27 2015
Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.
Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
Exported ZHS16GBK character set and Al16utf16 NCHAR character set
The server uses the Al32utf8 character set (possible character set conversions)
The specified table is about to be exported through the regular path ...
EXP-00006: An internal inconsistency error occurred
EXP-00000: Export termination failed
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. After the import, look at the definition of the table, become fixed
CREATE TABLE Call_log
(
LOG_ID Number (TEN) is 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
)
);
A small problem with the ability to automatically scale with Oracle 11g partitioning