impdp时parallel=4导致的错误

ORA-39121: Table "JUSTIN_a" can't be replaced, data will be skipped. Failing error is:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-00955: name is already used by an existing object
ORA-39121: Table "JUSTIN_b" can't be replaced, data will be skipped. Failing error is:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-00955: name is already used by an existing object
导入时候 遇到这两个表报错

SQL> select object_id,object_name from user_objects where object_name in('JUSTIN_a','JUSTIN_b');

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
     49194 JUSTIN_b
     49754 JUSTIN_a

SQL> select session_id,locked_mode, object_id from v$locked_object where object_id in (49194,49754);

SESSION_ID LOCKED_MODE  OBJECT_ID
---------- ----------- ----------
       386           3      49754
       258           3      49194

发现给这两个表上加了mode=3的锁，

接着把这两个表上的锁的会话删掉，其中JUSTIN_b可以正常导入，可是另外一个表仍然不行

[oracle@rac03 admin]$ impdp justin/justin directory=pump remap_schema=justindev:justin dumpfile=full_justindev_1228.dmp parallel=4 nologfile=y table_exists_action=append tables=justindev.JUSTIN_a

Import: Release 11.2.0.1.0 - Production on Wed Dec 29 14:17:14 2010

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

Connected 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
Master table "SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS_IMPORT_TABLE_01":  justin/******** directory=pump remap_schema=justindev:justin dumpfile=full_justindev_1228.dmp parallel=4 nologfile=y table_exists_action=append tables=justindev.JUSTIN_a
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "JUSTIN_a" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "JUSTIN_a" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS_IMPORT_TABLE_01" completed with 2 error(s) at 14:17:19

Google一大堆，有建议将parallel改为1的，尝试一下即可成功
[oracle@rac03 admin]$ impdp justin/justin directory=pump remap_schema=justindev:justin remap_tablespace=devdb1_01:justin_data,justin:justin_data,justin_index:justin_index dumpfile=full_justindev_1228.dmp parallel=1 nologfile=y table_exists_action=append tables=justindev.JUSTIN_a

Import: Release 11.2.0.1.0 - Production on Wed Dec 29 14:31:26 2010

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

Connected 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
Master table "SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS_IMPORT_TABLE_01":  justin/******** directory=pump remap_schema=justindev:justin remap_tablespace=devdb1_01:justin_data,justin:justin_data,justin_index:justin_index dumpfile=full_justindev_1228.dmp parallel=1 nologfile=y table_exists_action=append tables=justindev.JUSTIN_a
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "JUSTIN_a" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JUSTIN_a"                  854.1 MB 6185955 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS_IMPORT_TABLE_01" completed with 1 error(s) at 14:32:21

至此，所有import工作均已结束，查看表dba_datapump_jobs

SQL> select d.owner_name,d.job_name,d.operation,d.state from dba_datapump_jobs d;
 
OWNER_NAME                     JOB_NAME                       OPERATION                                                                        STATE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
justin                          SYS_IMPORT_SCHEMA_01           IMPORT                                                                           NOT RUNNING
justin                          SYS_IMPORT_SCHEMA_03           IMPORT                                                                           NOT RUNNING
justin                          SYS_EXPORT_SCHEMA_01           EXPORT                                                                           NOT RUNNING

尝试手工kill这几个job
[oracle@rac03 admin]$ impdp justin/justin attach=SYS_IMPORT_SCHEMA_01

Import: Release 11.2.0.1.0 - Production on Wed Dec 29 14:55:52 2010

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

Connected 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

Job: SYS_IMPORT_SCHEMA_01
  Owner: justin                         
  Operation: IMPORT                        
  Creator Privs: TRUE                          
  GUID: 986F775165E56B74E040007F010065A1
  Start Time: Wednesday, 29 December, 2010 14:55:54
  Mode: SCHEMA                        
  Instance: yhddb1
  Max Parallelism: 1
  EXPORT Job Parameters:
  IMPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        justin/******** parfile=impdp.par       
  State: IDLING                        
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
 
Worker 1 Status:
  Process Name: DW00
  State: UNDEFINED                     
  Object Schema: justin
  Object Name: JUSTIN_b
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1,281
  Worker Parallelism: 1

Import> status

Job: SYS_IMPORT_SCHEMA_01
  Operation: IMPORT                        
  Mode: SCHEMA                        
  State: IDLING                        
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
 
Worker 1 Status:
  Process Name: DW00
  State: UNDEFINED                     
  Object Schema: justin
  Object Name: JUSTIN_b
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1,281
  Worker Parallelism: 1

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

该job被成功kill

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-688841/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-688841/