在實際的工作環境下如果你在操作時將數據庫內的幾個數據字典表Truncate掉將會直接導致數據庫不能再繼續使用本文將針對一個相關案例進行詳細的講解
案例如下
數據庫環境Oracle數據庫 RAC（注由於數據庫的事務量體別大所以數據庫沒有進行備份）
檢查後發現的被截斷表
SQL> select object_nameobject_type from dba_objects where object_name like IDL%;
OBJECT_NAME OBJECT_TYPE
IDL_CHAR$ TABLE
IDL_SB$ TABLE
IDL_UB$ TABLE
IDL_UB$ TABLE
IDL_UB$表是特別重要的字典表只要出現故障數據庫就會出現大量的ORA錯誤所有事務將不能進行
ORA: internal error code arguments: []
[xCDDDFA] [] [] [] [] [] []
ORA 錯誤是一個特別難解決的問題問題出現後數據庫的某個跟蹤日志很快就會出現暴漲的情況因為IDL系列字典表是記錄數據庫對象編譯信息的丟失了其中的數據所有過程Package等都將無法執行
字典表作用的說明
IDL_UB$ is one of four tables that hold compiled PL/SQL code:
IDL_UB$
IDL_CHAR$
IDL_UB$
IDL_SB$
PL/SQL is based on the programming language Ada As a result PL/SQL uses a
variant of Descriptive Intermediate Attributed Notation for Ada (DIANA) which
is a treestructured intermediate language It is defined using a metanotation
called Interface Definition Language (IDL) DIANA provides for communication
internal to compilers and other tools
At compile time PL/SQL source code is translated into machinereadable
mcodeBoth the DIANA and mcode for a procedure or package are stored in the
databaseAt run time they are loaded into the shared (memory) pool The DIANA is
used to compile dependent procedures; the mcode is simply executed
These four tables hold the DIANA and the socode mcode I think mcode is
short for machinedependent byte code but there is a sizable machineindenpendent part
as well If you have a look at sqlbsq you can see
that Oracle documents the type column of these tables as follows:
part number not null
/* part: = diana = portable pcode
= machinedependentpcode */
如果出現更為嚴重的情況它將導致大量系統DBMS包失效其重新編譯也將更為復雜
恢復數據庫消除所有ORA錯誤的方法
恢復的方法是通過運行相關的腳本重建和重新編譯所有Procedure/Trigger/Package等對象重新生成這些對象的DIANA和socode mcode主要包括catlogsqlcatprocsql等腳本
注意即使以花費大量的時間為代價一些ORA錯誤也必須解決
From:http://tw.wingwit.com/Article/program/SQL/201311/16346.html
