导入分区表的某分区报错: ORA-39151: Table "SYSTEM"."T_PART" exists.

最新推荐文章于 2024-07-25 08:45:00 发布

宋小荣

最新推荐文章于 2024-07-25 08:45:00 发布

阅读量4k

点赞数

CC 4.0 BY-SA版权

分类专栏：故障处理文章标签：数据泵导出导入分区表 ORA-39151

本文链接：https://blog.youkuaiyun.com/robo23/article/details/42192221

故障处理专栏收录该内容

32 篇文章

订阅专栏

本文介绍了分区表的创建、导出及导入过程，并详细解释了如何使用不同参数解决导入时遇到的问题，如使用TABLE_EXISTS_ACTION参数来避免错误。

分区表的导出/导入实验过程如下:

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。

可以将表的每个分区当作是一张表来理解。