今天遇到的有关ORA-04020: deadlock detected while trying to lock object LBACSYS.L

事情的起因是我要将当前一个在线的数据库imp到一个测试库进行测试。
exp很顺利，没有问题
在测试库上drop user * cascade;
然后重新建用户，然后把刚才的备份都导入，都很顺利。

然后我要用pl/sql developer连接，察看导入的表的时候，提示ORA-00942：表或试图不存在。
我有一点心不在焉的，随便百度了一把，看到有人说应该执行：
@$ORACLE_HOME/rdbms/admin/catproc.sql
我就执行了，提示很多错误，以下是第一个：
create or replace package dbms_standard is
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_CREATE_CTABLE_AFTER
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "LBACSYS.LBAC_EVENTS" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

。。。。。。

我都不是很上心，想着没有关系，这些包未见得有用：
然后我可能有点儿晕了，以为还没有imp，就继续imp：
但是这时候开始，imp提示错误了：
Import: Release 9.2.0.1.0 - Production on Thu Feb 24 13:49:37 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

IMP-00058: ORACLE error 4045 encountered
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC$LOGON
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2Username: pointcard
Password:

我想那就重新编译呗，就这么三板斧，呵呵：
@$ORACLE_HOME/rdbms/admin/utlrp.sql
提示错误：
PL/SQL procedure successfully completed.

CREATE TABLE utl_recomp_invalid(obj# number)
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_CREATE_CTABLE_BEFORE
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
.......

这时候发现客户端已经连不上了，还是提示LBACSYS.LBAC_EVENTS的错误。我有点儿傻了，虽然是测试库，但是还是有正在测试的应用在用的，而正因为是测试库，所以没有每天备份，好在最后一次备份是大前天，也就是还不是很麻烦，暗自庆幸当时随手备了一个。

再往下我决定重启试试，我甚至都做好了启不了重建一个数据库的准备了，有备份在手，就是比较有信心，呵呵。

shutdown immediate 没有反应

kill掉 showdown abort
再startup再shutdown immediate 都没有问题
察看alert.log，都是刚才执行.sql的错误：
Thu Feb 24 14:21:08 2005
Errors in file /home/oracle/admin/dwtest/udump/dwtest_ora_23467.trc:
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC$LOGON
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

再去百度，这次比较细心了，看到Fenng在itpub的文章提到类似的问题：http://www.itpub.net/238819.html
他是在exp提示9204的时候执行了$ORACLE_HOME/rdbms/admin/catpatch.sql
并且执行了两遍，然后发现客户端连不上了。跟我的过程有一点像，再往下看到了解决方案：
The reason for this problem seems to be an Upgrade for Label-Security
even if it's not installed. 　//Label security 没有安装，居然补丁去默认给升级?　

Solution:

shutdown immediate;
startup migrate;
alter view lbacsys.lbac$all_table_policies compile;
alter package lbacsys.lbac_events compile body;
shutdown immediate;
startup;

按照上面步骤的执行，就没有问题。

非常感谢Fenng给我的这个帮助，尽管他应该不会看到这里啦，哈哈哈。

最后发现pl/sql developer提示9204是需要:
GRANT SELECT ANY TABLE TO *;
GRANT SELECT ANY SEQUENCE TO *;
GRANT SELECT ANY DICTIONARY TO *;

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

转载于:http://blog.itpub.net/51862/viewspace-180602/