利用hcheck检查数据字典一致性状态

利用hcheck可以检查oracle数据字典的一致性状态，主要排查用户或对象无法删除的原因.

其操作步骤如下：

 

   1. Connect as SYS schema in sqlplus

2. Create package hOut as described in Note 101468.1

3. Create package hcheck in SYS schema (Refer the attachment under SCRIPT to Create package hcheck  

4. spool outputfile

5. execute hcheck.full

6. Output will go to the spool file and the session trace file.
   The script will report various dictionary related issues
   which may or may not be a problem - Any problems reported 
   should be reviewed by an experienced support analyst as some
   reported "problems" may be normal and expected.

 

具体操作步骤如下：

SQL> conn /@test01 as sysdba
已连接。
SQL>

SQL> create or replace package hOut as
  2   --
  3   -- Output options - change these to default as required
  4   -- You can override them at run time if required.
  5   --
  6    TO_DBMS_OUTPUT boolean := TRUE; -- Send output to DBMS_OUTPUT
  7    TO_USER_TRACE  boolean := TRUE; -- Send output to user trace file
  8    IGNORE_ERRORS  boolean := TRUE; -- Ignore DBMS_OUTPUT errors if
  9       -- also writing to the trace file
 10   --
 11   -- Output methods
 12   --
 13    procedure put_line(txt varchar2);
 14    procedure put(txt varchar2);
 15    procedure new_line;
 16    procedure wrap(txt varchar2, linelen number default 78);
 17    procedure rule_off;
 18   --
 19  end hOut;
 20  /

程序包已创建。

SQL> show errors
没有错误。
SQL> create or replace package body hOut as
  2    -- 7.3 has problems with ksdwrt as it uses the wrong length info
  3    -- putting nonsense on the end of lines.
  4    -- As a workaround we copy the text to a TMP varchar, append a chr(0)
  5    -- then reset the length back so we have an hidden chr(0) at the end
  6    -- of the string.
  7    tmp varchar2(2001);
  8    --
  9    APP_EXCEPTION EXCEPTION;
 10    pragma exception_init(APP_EXCEPTION, -20000);
 11    --
 12    procedure put_line(txt varchar2) is
 13    begin
 14      tmp:=txt||chr(0);
 15      tmp:=txt;
 16      if TO_DBMS_OUTPUT then
 17        begin
 18   dbms_output.put_line(txt);
 19        exception
 20   when APP_EXCEPTION then
 21     -- If DBMS_OUTPUT is full then carry on if we are writing to
 22     -- the trace file and ignoring errors, otherwise error now
 23     if TO_USER_TRACE and IGNORE_ERRORS then
 24       begin
 25         dbms_output.put_line('[TRUNCATED]');
 26              exception
 27         when APP_EXCEPTION then
 28      null;
 29       end;
 30     else
 31       raise;
 32     end if;
 33        end;
 34      end if;
 35      if TO_USER_TRACE then
 36   dbms_system.ksdwrt(1,tmp);
 37      end if;
 38    end;
 39   --
 40    procedure put(txt varchar2) is
 41    begin
 42      tmp:=txt||chr(0);
 43      tmp:=txt;
 44      if TO_DBMS_OUTPUT then
 45        begin
 46   dbms_output.put(txt);
 47        exception
 48   when APP_EXCEPTION then
 49     -- If DBMS_OUTPUT is full then carry on if we are writing to
 50     -- the trace file and ignoring errors, otherwise error now
 51     if TO_USER_TRACE and IGNORE_ERRORS then
 52       begin
 53         dbms_output.put('[TRUNCATED]');
 54              exception
 55         when APP_EXCEPTION then
 56      null;
 57       end;
 58     else
 59       raise;
 60     end if;
 61        end;
 62      end if;
 63      if TO_USER_TRACE then
 64   dbms_system.ksdwrt(1,tmp);
 65      end if;
 66    end;
 67   --
 68    procedure new_line is
 69    begin
 70      if TO_DBMS_OUTPUT then
 71        begin
 72   dbms_output.new_line;
 73        exception
 74   when APP_EXCEPTION then
 75     if TO_USER_TRACE and IGNORE_ERRORS then
 76       null;
 77     else
 78       raise;
 79     end if;
 80        end;
 81      end if;
 82      if TO_USER_TRACE then
 83   dbms_system.ksdwrt(1,' ');
 84      end if;
 85    end;
 86   --
 87    procedure wrap(txt varchar2, linelen number default 78) is
 88      p   integer:=1;
 89      len integer;
 90      pos integer;
 91      chunk varchar2(2000);
 92      xchunk varchar2(2000);
 93      llen number:=linelen;
 94    BEGIN
 95      if (llen>2000) then
 96   llen:=2000;
 97      end if;
 98      if (llen<=1) then
 99   llen:=78;
100      end if;
101      len:=length(txt);
102      while (p<=len) loop
103        chunk:=substr(txt,p,llen);
104        pos:=instr(chunk,chr(10),-1);
105        if pos>0 then
106         -- We have a CR in the text - use it
107         put_line(substr(chunk,1,pos-1));
108         p:=p+pos;
109        else
110         -- No CR in the text so we will look for a split character
111         xchunk:=translate(chunk,' ,()=',',,,,,');
112         pos:=instr(xchunk,',',-1);
113         if pos>0 and len>llen then
114          put_line(substr(chunk,1,pos));
115   p:=p+pos;
116         else
117          put(chunk);
118   p:=p+llen;
119         end if;
120        end if;
121      end loop;
122      new_line;
123    END;
124   --
125    procedure rule_off is
126    begin
127      put_line('=========================================================');
128    end;
129   --
130  begin
131    dbms_output.enable(100000);
132  end hout;
133  /

程序包体已创建。

SQL> @"C:\Documents and Settings\Administrator.DREAM\桌面\hcheck\hcheck.sql"

程序包已创建。

没有错误。

程序包体已创建。

没有错误。
HCheck Version 8i-11/1.80

Found 0 potential problems and 0 warnings

PL/SQL 过程已成功完成。

SQL> execute hcheck.full
HCheck Version 8i-11/1.80

Found 0 potential problems and 0 warnings

PL/SQL 过程已成功完成。

SQL>

 

 

 

引自metalink文档:

 

Abstract
Pro-actively detecting common data dictionary problems
Product Name, Product Version	RDBMS, 8.1 to 11.2
Current Release	1.80
Date	29-NOV-2010 15:15
Platform	Platform Independent
Date Created	23-MAR-2001
Instructions
Execution Environment: Access Privileges: Requires to be run connected as SYS schema Usage: $ sqlplus SQL*Plus: Release 9.2.0.2.0 - Production on Mon Nov 11 12:00:06 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production SQL> set serveroutput on SQL> spool outputfile SQL> execute hcheck.full Instructions: 1. Connect as SYS schema in sqlplus 2. Create package hOut as described in Note:101468.1 3. Create package hcheck in SYS schema (Refer the attachment under SCRIPT to Create package hcheck 4. spool outputfile 5. execute hcheck.full 6. Output will go to the spool file and the session trace file. The script will report various dictionary related issues which may or may not be a problem - Any problems reported should be reviewed by an experienced support analyst as some reported "problems" may be normal and expected. PROOFREAD THE SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.The script will produce an output file named [outputfile]. This file can be viewed in a browser or uploaded for support analysis.
Description
To provide a single package which looks for common data dictionary problems. The script can be used with Oracle versions 8.1, 9.0, 9.2, 10.1, 10.2, 11.1 and 11.2. It checks consistency of certain dictionary relationships and looks for certain known issues - certain reported "problems" will be normal and expected. This script is for use mainly under the guidance of Oracle Support.