在实际的工作环境下如果你在操作时将数据库内的几个数据字典表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错误也必须解决
