ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 导入数据时遇见ORA-00054

导入数据时遇见ORA-00054

原创 Linux操作系统 作者:杨奇龙 时间:2011-04-01 23:57:31 0 删除 编辑
今天应开发人员要求对一个表进行导入。发现如下错误:
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7700118