oracle 错误 04020,今天遇到的有关ORA-04020: deadlock detected while trying to lock object LBACSYS.LBAC_EVENTS错...

事情的起因是我要将当前一个在线的数据库imp到一个测试库进行测试。

exp很顺利，没有问题

在测试库上drop user * cascade;

然后重新建用户，然后把刚才的备份都导入，都很顺利。[@more@]

然后我要用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 *;