创建分区表实验：

创建分区表实验：
创建Partition Table:
create table test10 (sal_date date not null,
sal_id number not null,
sal_row number(12) not null)
partition by range(sal_date)
(
partition p_11 values less than(to_date(‘2012-01-01’,’yyyy-mm-dd’)),
partition p_12 values less than(to_date(‘2013-01-01’,’yyyy-mm-dd’)),
partition p_13 values less than(to_date(‘2014-01-01’,’yyyy-mm-dd’)),
partition p_14 values less than(to_date(‘2015-01-01’,’yyyy-mm-dd’)),
partition p_15 values less than(to_date(‘2016-01-01’,’yyyy-mm-dd’)),
partition p_16 values less than(to_date(‘2017-01-01’,’yyyy-mm-dd’)),
partition p_other values less than(maxvalue)
);

像分区表中插入数据：
insert into test10 SELECT TRUNC(SYSDATE)-ROWNUM, dbms_random.random,ROWNUM FROM dual CONNECT BY LEVEL<=3000;<>

创建Unique Index:
CREATE UNIQUE INDEX test10_ind1 ON test10(sal_date);

查询分区表
select * from test10 partition(p_12)

导出分区表的p_12分区
[oracle@OCP dump]$ expdp system/oracle directory=dump dumpfile=p_12.dmp logfile=p_12.log tables=fwj.test10:p_12

Export: Release 11.2.0.4.0 - Production on Wed Jul 1 16:02:29 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01”: system/** directory=dump dumpfile=p_12.dmp logfile=p_12.log tables=fwj.test10:p_12
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported “FWJ”.”TEST10”:”P_12” 14.20 KB 366 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01” successfully loaded/unloaded

---

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oradata/dump/p_12.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01” successfully completed at Wed Jul 1 16:02:41 2015 elapsed 0 00:00:11

删除p_12分区
SQL> alter table test10 drop partition p_12;
Table altered.
分区已不存在了
SQL> select * from test10 partition(p_12);
select * from test10 partition(p_12)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist

导入p_12分区
[oracle@OCP dump]$ impdp system/oracle directory=dump dumpfile=p_12.dmp logfile=p_12.log tables=fwj.test10:p_12 table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Wed Jul 1 16:08:28 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01” successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01”: system/** directory=dump dumpfile=p_12.dmp logfile=p_12.log tables=fwj.test10:p_12 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “FWJ”.”TEST10”:”P_12” 14.20 KB 366 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_TABLE_01” successfully completed at Wed Jul 1 16:08:31 2015 elapsed 0 00:00:02

已恢复p_12分区
SQL> select * from test10 partition(p_12);
SAL_DATE SAL_ID SAL_ROW

---

14-JAN-12 -690306394 1264
13-JAN-12 -1.081E+09 1265
12-JAN-12 645925840 1266
11-JAN-12 -815719932 1267
10-JAN-12 395182289 1268
09-JAN-12 765673606 1269
08-JAN-12 1428649643 1270
07-JAN-12 864599827 1271
06-JAN-12 1540348225 1272
05-JAN-12 -1.479E+09 1273
04-JAN-12 -1.329E+09 1274

SAL_DATE SAL_ID SAL_ROW

---

03-JAN-12 1541487273 1275
02-JAN-12 557134644 1276
01-JAN-12 695011213 1277
………………………………………………..