导入数据时遇见ORA-00054

今天应开发人员要求对一个表进行导入。发现如下错误：
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-00955: name is already used by an existing object
经过查看锁的使用情况，发现有开发人员对要导入的表进行dml操作。
下面模拟一下环境：、
session 1：
yang@rac1>update t set b=1 where a=4;
1 row updated.
导出数据：
oracle@rac1:rac1 /tmp/dump>impdp yang/yang directory=dumpdir  dumpfile=tm.dmp  tables=t  exclude=index
Import: Release 11.2.0.1.0 - Production on Fri Apr 1 23:41:48 2011
Data Mining and Real Application Testing options
Master table "YANG"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "YANG"."SYS_IMPORT_TABLE_01":  yang/******** directory=dumpdir dumpfile=tm.dmp tables=t exclude=index
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "YANG"."T" 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 "YANG"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 23:42:51
使用  table_exists_action=skip （默认值）时，没有报错。但是使用 table_exists_action=replace 是 要先对表进行drop，然后重建并导入。此时报错。
oracle@rac1:rac1 /tmp/dump>impdp yang/yang directory=dumpdir  dumpfile=tm.dmp table_exists_action=replace  tables=t  exclude=index
Import: Release 11.2.0.1.0 - Production on Fri Apr 1 23:45:00 2011
Master table "YANG"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "YANG"."SYS_IMPORT_TABLE_01":  yang/******** directory=dumpdir dumpfile=tm.dmp table_exists_action=replace tables=t exclude=index
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39121: Table "YANG"."T" 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
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "YANG"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 23:46:01

查看lock的使用情况：
yang@rac1>SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_NAME='T' AND WNER='YANG';
 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
    130763 T
yang@rac1>@lock
lock                         lock                                                      blocked
address           session id type         id1       id2 lock mode  req mode      BLOCK   sessid
---------------- ----------- ------ --------- --------- --------- --------- ---------- --------
000000018B9B18E8         959 XR             4         0         1         0    2
000000018B9B22A8         385 RT             1         2         6         0    2
00002B79A83626F0           5 TM        130763         0         3         0    2  ====》该表上有tm锁
000000018B9B38C8        1344 AE           100         0         4         0    2
在对正在使用的数据库进行导入数据时（某个表）最后先确认是否正在使用。然后再导入。当然每个案例都有所不同，仁者见仁智者见智了。
好了，睡觉。。

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

转载于:http://blog.itpub.net/22664653/viewspace-691506/