熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

數據字典表Truncate掉將導致數據庫不可用

2013-11-13 12:39:54  來源: SQL語言 

  在實際的工作環境下如果你在操作時將數據庫內的幾個數據字典表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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.