分区表的导出/导入实验过程如下:
1. 创建测试分区表
CREATE TABLE "SYSTEM"."T_PART"
("OWNER" VARCHAR2(30),
"SEGMENT_NAME" VARCHAR2(81),
"PARTITION_NAME" VARCHAR2(30),
"SEGMENT_TYPE" VARCHAR2(18),
"SEGMENT_SUBTYPE" VARCHAR2(10),
"TABLESPACE_NAME" VARCHAR2(30),
"HEADER_FILE" NUMBER,
"HEADER_BLOCK" NUMBER,
"BYTES" NUMBER,
"BLOCKS" NUMBER,
"EXTENTS" NUMBER,
"INITIAL_EXTENT" NUMBER,
"NEXT_EXTENT" NUMBER,
"MIN_EXTENTS" NUMBER,
"MAX_EXTENTS" NUMBER,
"MAX_SIZE" NUMBER,
"RETENTION" VARCHAR2(7),
"MINRETENTION" NUMBER,
"PCT_INCREASE" NUMBER,
"FREELISTS" NUMBER,
"FREELIST_GROUPS" NUMBER,
"RELATIVE_FNO" NUMBER,
"BUFFER_POOL" VARCHAR2(7),
"FLASH_CACHE" VARCHAR2(7),
"CELL_FLASH_CACHE" VARCHAR2(7)
) partition by hash(segment_type)
( partition p1
, partition p2
, partition p3
, partition p4
);
insert into t_part select * from dba_segments;
commit;
2. 导出分区表
[oracle@rac1 expdp]$ expdp system/oracle tables=t_part directory=expdp logfile=expdp_t_part.log job_name=1
...
. . exported "SYSTEM"."T_PART":"P1" 144.7 KB 953 rows
. . exported "SYSTEM"."T_PART":"P2" 31.17 KB 114 rows
. . exported "SYSTEM"."T_PART":"P3" 118.1 KB 781 rows
. . exported "SYSTEM"."T_PART":"P4" 466.4 KB 3604 rows
Master table "SYSTEM"."1" successfully loaded/unloaded
随后将T_PART表删除:
SQL> DROP TABLE T_PART PURGE;
3. 导入测试1(分区p2)
[oracle@rac1 expdp]$ impdp system/oracle directory=expdp dumpfile=expdat.dmp logfile=impdp_t_part2.log job_name=2 tables=t_part:p2
...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."T_PART":"P2" 31.17 KB 114 rows <<<<<<<<===========只有分区p2数据被导入
Job "SYSTEM"."2" successfully completed at Sat Dec 27 12:30:50 2014 elapsed 0 00:00:05
此时,检查T_PART的表结构,可看到p1,p2,p3,p4分区都已被自动创建,因为导入时候会自动创建表结构,所有分区都是表的结构中一部分。
4. 导入测试2(分区p3)
[oracle@rac1 expdp]$ impdp system/oracle directory=expdp dumpfile=expdat.dmp logfile=impdp_t_part3.log job_name=3 tables=t_part:p3
...
ORA-39151: Table "SYSTEM"."T_PART" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."3" completed with 1 error(s) at Sat Dec 27 12:32:16 2014 elapsed 0 00:00:03
TABLE_EXISTS_ACTION默认是skiped,意为如果IMPDP检测到待导的表名已存在于数据中,则略过。添加参数重来一遍:
[oracle@rac1 expdp]$ impdp system/oracle directory=expdp dumpfile=expdat.dmp logfile=impdp_t_part3.log job_name=3 tables=t_part:p3 TABLE_EXISTS_ACTION=append
...
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SYSTEM"."T_PART" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."T_PART":"P3" 118.1 KB 781 rows
Job "SYSTEM"."3" successfully completed at Sat Dec 27 12:33:47 2014 elapsed 0 00:00:11
有关TABLE_EXISTS_ACTION参数的说明如下:
TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}
The possible values have the following effects:
•
SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
•
APPEND loads rows from the source and leaves existing rows unchanged.
•
TRUNCATE deletes existing rows and then loads rows from the source.
•
REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
至此,在分区表的导出导入实验中,通过添加TABLE_EXISTS_ACTION=APPEND来解决了ORA-39151。
可以将表的每个分区当作是一张表来理解。