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

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

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

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

impdp時parallel=4導致的錯誤

相關文章