oracle 9i刪除public使用者造成資料字典損壞所有sql語句不能操作的故障處理

作業系統sun,oracle 9.2.0.8.一開始維護人員新建一個使用者只授予connect許可權.用新建使用者登入後發現能查詢生產使用者的資料.然後從dba_sys_privs檢視將public使用者給刪除了.然後應用程式執行sql語句就報ORA-06553：PLS-213：Standard包不可訪問的問題,解決這個問題的方法是執行catalog.sql和catproc.sql重建資料字典.
SQL>sqlplus /nolog
SQL> conn sys/密碼 as sysdba
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/dbmsutil.sql
SQL>alter package standard compile
SQL>alter package dbms_standard compile
ORA-04020: deadlock detected while trying to lock object SYS.CDC_ALTER_CTABLE_BEFORE
在執行alter package dbms_standard compile語句時出錯了

在MOS上有一篇關於oracle 9.2.0.8中關於ORA-04020錯誤的bug,資訊如下:
Researching the issue on ora-4020 and SYS.CDC_CREATE_CTABLE_BEFORE lead to BUG 3228083 which was
experiencing similar problems on the same object. This bug was closed as a duplicate of bug 3017048.
Internal BUG:3228083 – Appsst10g:R8:Utlrcmp Error: Ora-04045: Sys.Cdc_Create_Ctable_Before

解決方法， 用spfile 建立pfile, 在pfile裡新增如下內容，然後用修改之後的pfile啟動資料庫，在執行指令碼。
_system_trig_enabled=false
aq_tm_processes=0
job_queue_processes=0
指令碼執行完後，在去掉這些引數，正常啟動資料庫即可

在成功執行alter package dbms_standard compile語句後再執行下面的指令碼來編譯無效物件:
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
然而再次遇到bug
ORA-00600: internal error code, arguments: [4412], [0x8BAA483C], [0x0], [], [], [], [], []

在mos上ORA-00600 [4406] or ORA-00600 [4412] in alert Log (文件 ID 742118.1)是關於這個bug的描述
In this Document
Symptoms
Cause
Solution
References
APPLIES TO:

Oracle Server – Enterprise Edition – Version 9.2.0.1 to 9.2.0.8 [Release 9.2]
Information in this document applies to any platform.
***Checked for relevance on 10-Aug-2012***
SYMPTOMS

On Oracle 9i, the following errors could be reported in the alert log

ORA-00600: internal error code, arguments: [4412], [0x8BAA483C], [0x0], [], [], [], [], []
ORA-00600: internal error code, arguments: [4406], [0x8BAA483C], [0x0], [], [], [], [], []

The Call Stack should look something like:

ktcrcm ktcsod kssdch_stage ksuxds ksudel opilof opiodr ttcpip opitsk opiino opiodr opidrv sou2o

CAUSE

Unpublished Bug 2628920 ORA-600 [4412] AND [4406] WHEN EXITING SESSION AFTER CANCELLING DELETESCHEMA.

The bug explains that the problem may occur when the user terminates or cancels a ‘Delete schema’ operation using CTRL + C for example. The function opilof in the Call Stack above is an indication that the error is only seen at session log off.

SOLUTION

1. Upgrade to 10g where the bug is fixed.

OR

2. Ignore the error as it is completely harmless, the error is generated at session log off.

Note that in some cases the errors have also been encountered on Oracle 10.2 releases. These may be related to a different bug, but remain harmless.
REFERENCES

@ BUG:2628920 – ORA-600:[4412] AND [4406] WHEN EXITING SESSION AFTER CANCELLING DELETESCHEMA

解決方法是升級到oracle 10g.這裡選擇手工對無效物件進行編譯.