导入数据时遇见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

在对正在使用的数据库进行导入数据时（某个表）最后先确认是否正在使用。然后再导入。当然每个案例都有所不同，仁者见仁智者见智了。

好了，睡觉。。