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

數據庫相關:如何使用DBMS

2013-11-13 12:34:37  來源: SQL語言 

  二使用DBMS_REPAIRCHECK_OBJECT進行檢測

  CHECK_OBJECT procedure檢查指定的object並且將關於損壞和修補的指導信息裝入Repair Table它將效驗指定object中所有塊的一致性而在此之前已標識的塊就會被跳過

  SQL> @checkObject
  SQL> set serveroutput on
  SQL>
  SQL> declare
   rpr_count int;
   begin
   rpr_count := ;
   dbms_repaircheck_object (
   schema_name => SYSTEM
   object_name => T
   repair_table_name => REPAIR_TABLE
   corrupt_count => rpr_count);
   dbms_outputput_line(repair count: || to_char(rpr_count));
   end;
   /
  repair count:
  PL/SQL procedure successfully completed
  repair_table的結構如下
  SQL> desc repair_table
  Name Null? Type
  
  OBJECT_ID NOT NULL NUMBER
  TABLESPACE_ID NOT NULL NUMBER
  RELATIVE_FILE_ID NOT NULL NUMBER
  BLOCK_ID NOT NULL NUMBER
  CORRUPT_TYPE NOT NULL NUMBER
  SCHEMA_NAME NOT NULL VARCHAR()
  OBJECT_NAME NOT NULL VARCHAR()
  BASEOBJECT_NAME VARCHAR()
  PARTITION_NAME VARCHAR()
  CORRUPT_DESCRIPTION VARCHAR()
  REPAIR_DESCRIPTION VARCHAR()
  MARKED_CORRUPT NOT NULL VARCHAR()
  CHECK_TIMESTAMP NOT NULL DATE
  FIX_TIMESTAMP DATE
  REFORMAT_TIMESTAMP DATE

  我們可以從repair_table中查詢壞塊的情況

  SQL> select object_name block_id corrupt_type marked_corrupt
   corrupt_description repair_description
   from repair_table;
  OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
  
  CORRUPT_DESCRIPTION
  
  REPAIR_DESCRIPTION
  
  T FALSE
  kdbchk: row locked by nonexistent transaction
  table= slot=
  lockid= ktbbhitc=
  mark block software corrupt

  三從壞塊中進行數據抽取

  從repair_table中可以知道file 的block 壞了但注意此時這個塊還沒有被標識為壞塊因此要在這個時候將任何有意義的數據趕快抽取出來一旦該塊被標識為壞塊整個塊就會被跳過

   通過ALTER SYSTEM DUMP或trace中來獲取塊中包含的記錄數 (nrows = )

   查詢損壞的object盡量抽取盡可能多的信息

  下面的查詢可以用來從壞塊中搶救數據

  建立一個臨時表(temp_t)以方便數據的插入

  SQL> create table temp_t as
   select * from systemt
   where dbms_rowidrowid_block_number(rowid) =
   and dbms_rowidrowid_to_absolute_fno (rowid SYSTEMT) = ;
  Table created
  SQL> select col from temp_t;
  COL
  
  
  

  四使用DBMS_REPAIRFIX_CORRUPT_BLOCKS來標識壞塊

  FIX_CORRUPT_BLOCKS procedure用來根據repair table中的信息修正指定objects中的壞塊當這個塊被標識為壞了以後做全表掃描將引起ORA

  SQL> declare
   fix_count int;
   begin
   fix_count := ;
   dbms_repairfix_corrupt_blocks (
   schema_name => SYSTEM
   object_name => T
   object_type => dbms_repairtable_object
   repair_table_name => REPAIR_TABLE
   fix_count => fix_count);
   dbms_outputput_line(fix count: || to_char(fix_count));
   end;
   /
  fix count:
  PL/SQL procedure successfully completed

  查詢repair_table可以看到block 已經被標識

  SQL> select object_name block_id marked_corrupt
   from repair_table;
  OBJECT_NAME BLOCK_ID MARKED_COR
  
  T TRUE

  這時再對table t做全表掃描ORA將會出現

  SQL> select * from systemt;
  select * from systemt
  *
  ERROR at line :
  ORA: ORACLE data block corrupted (file # block # )
  ORA: data file : /tmp/ts_corruptdbf

[]  []  []  


From:http://tw.wingwit.com/Article/program/SQL/201311/16168.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.