Data Dictionary Inconsistency
                                                 Last update (2008-06-12 12:23:09)
                                                                                                                   Date added (2008-06-12 11:13:07)

Summary
For different reasons you may end up with dictionary inconsistencies. For example when pressing CTRL-C during drop of functional based index

The most common data dictionary problems are:
1. Data Dictionary Inconsistency, missing rows in tables:
          - Tab$/Ind$ with no entries in OBJ$
          - Undo$/Tab$/Ind$ with no entries in SEG$
          - Seg$ with no entries in TAB$/IND$/OBJ$
2. Missing data dictionary objects
3. Corrupted data dictionary objects (table, index, or table-index inconsistency)
4. Invalid entries in data dictionary tables.
How to find Inconsistencies
In order to find the dictionary possible problems you must run as sysdba the hcheck.full procedure, for more details how to setup the package refer to Metalink Note 136697.1 Before running the hckeck.full procedure you must setup the hOut package

hOut package
create or replace package hOut as
 -- 
 -- Output options - change these to default as required
 -- You can override them at run time if required.
 --
  TO_DBMS_OUTPUT boolean := TRUE;	-- Send output to DBMS_OUTPUT
  TO_USER_TRACE  boolean := TRUE;	-- Send output to user trace file
  IGNORE_ERRORS  boolean := TRUE;	-- Ignore DBMS_OUTPUT errors if
					-- also writing to the trace file
 --
 -- Output methods
 --
  procedure put_line(txt varchar2);
  procedure put(txt varchar2);
  procedure new_line;
  procedure wrap(txt varchar2, linelen number default 78);
  procedure rule_off;
 --
end hOut;
/
show errors
create or replace package body hOut as
  -- 7.3 has problems with ksdwrt as it uses the wrong length info
  -- putting nonsense on the end of lines.
  -- As a workaround we copy the text to a TMP varchar, append a chr(0)
  -- then reset the length back so we have an hidden chr(0) at the end
  -- of the string.
  tmp varchar2(2001);
  --
  APP_EXCEPTION EXCEPTION;
  pragma exception_init(APP_EXCEPTION, -20000);
  --
  procedure put_line(txt varchar2) is
  begin
    tmp:=txt||chr(0);
    tmp:=txt;
    if TO_DBMS_OUTPUT then
      begin
	dbms_output.put_line(txt);
      exception
	when APP_EXCEPTION then
	  -- If DBMS_OUTPUT is full then carry on if we are writing to
	  -- the trace file and ignoring errors, otherwise error now
	  if TO_USER_TRACE and IGNORE_ERRORS then
	    begin
	      dbms_output.put_line('[TRUNCATED]');
            exception
	      when APP_EXCEPTION then
		  null;
	    end;
	  else
	    raise;
	  end if;
      end;
    end if;
    if TO_USER_TRACE then
	dbms_system.ksdwrt(1,tmp);
    end if;
  end;
 --
  procedure put(txt varchar2) is
  begin
    tmp:=txt||chr(0);
    tmp:=txt;
    if TO_DBMS_OUTPUT then
      begin
	dbms_output.put(txt);
      exception
	when APP_EXCEPTION then
	  -- If DBMS_OUTPUT is full then carry on if we are writing to
	  -- the trace file and ignoring errors, otherwise error now
	  if TO_USER_TRACE and IGNORE_ERRORS then
	    begin
	      dbms_output.put('[TRUNCATED]');
            exception
	      when APP_EXCEPTION then
		  null;
	    end;
	  else
	    raise;
	  end if;
      end;
    end if;
    if TO_USER_TRACE then
	dbms_system.ksdwrt(1,tmp);
    end if;
  end;
 --
  procedure new_line is
  begin
    if TO_DBMS_OUTPUT then
      begin
	dbms_output.new_line;
      exception
	when APP_EXCEPTION then
	  if TO_USER_TRACE and IGNORE_ERRORS then
	    null;
	  else
	    raise;
	  end if;
      end;
    end if;
    if TO_USER_TRACE then
	dbms_system.ksdwrt(1,' ');
    end if;
  end;
 --
  procedure wrap(txt varchar2, linelen number default 78) is
    p   integer:=1;
    len integer;
    pos integer;
    chunk varchar2(2000);
    xchunk varchar2(2000);
    llen number:=linelen;
  BEGIN
    if (llen>2000) then
	llen:=2000;
    end if;
    if (llen<=1) then
	llen:=78;
    end if;
    len:=length(txt);
    while (p<=len) loop
      chunk:=substr(txt,p,llen);
      pos:=instr(chunk,chr(10),-1);
      if pos>0 then
       -- We have a CR in the text - use it
       put_line(substr(chunk,1,pos-1));
       p:=p+pos;
      else 
       -- No CR in the text so we will look for a split character
       xchunk:=translate(chunk,' ,()=',',,,,,');
       pos:=instr(xchunk,',',-1);
       if pos>0 and len>llen then
        put_line(substr(chunk,1,pos));
	p:=p+pos;
       else
        put(chunk);
	p:=p+llen;
       end if;
      end if;
    end loop;
    new_line;
  END;
 --
  procedure rule_off is
  begin
    put_line('=========================================================');
  end;
 --
begin
  dbms_output.enable(100000);
end hout;
/
hcheck.full procedure
REM ======================================================================
REM hcheck8i.sql		Version 1.30	10 Aug 2007
REM
REM Purpose:
REM	To provide a single package which looks for common data dictionary
REM	problems. 
REM 	Note that this version has not been checked with locally managed
REM 	tablespaces and may give spurious output if these are in use.
REM 	This script is for use mainly under the guidance of Oracle Support.
REM 
REM Usage:
REM 	set serverout on
REM 	execute hcheck.full;
REM 
REM 	Output is to the hOut package to allow output to be redirected
REM 	as required
REM   
REM     See  for details of using this and other h* packages
REM
REM Depends on:
REM	hOut 
REM
REM Notes:
REM 	Must be installed in SYS schema
REM	This package is intended for use in Oracle 8.1, 9.0 and 9.2
REM      This package will NOT work in 8.0 or earlier.
REM     In all cases any output reporting "problems" should be 
REM      passed by an experienced Oracle Support analyst to confirm
REM      if any action is required.
REM
REM CAUTION
REM   The sample program in this article is provided for educational 
REM   purposes only and is NOT supported by Oracle Support Services.  
REM   It has been tested internally, however, and works as documented.  
REM   We do not guarantee that it will work for you, so be sure to test 
REM   it in your environment before relying on it.
REM 
REM ======================================================================
REM
create or replace package hcheck as
  procedure Full;
end hcheck;
/
show errors
create or replace package body hcheck as
 --
  Ver	VARCHAR2(10)  := '8i/1.30';
  Warn  NUMBER  :=0;
  Fatal NUMBER  :=0;
 --
  Function Owner( uid number ) return varchar2 is
    r varchar2(30):=null;
  begin
    select name into r from user$ where user#=uid;
    return(r);
  exception
    when no_data_found then
	return('*UnknownOwnerID='||uid||'*');
  end;
 --
  Function ObjName( objid number ) return varchar2 is
    r varchar2(40):=null;
    own number;
  begin
    select name , owner# into r,own from obj$ where obj#=objid;
    return(owner(own)||'.'||r);
  exception
    when no_data_found then
	return('*UnknownObjID='||objid||'*');
  end;
 --
  procedure OversizedFiles is
   Cursor cBigFile is
	select f.ts# TS, f.relfile# RFILE, f.file# AFILE, v.name NAME, f.blocks
	  from file$ f, v$datafile v
  	 where f.blocks>4194303 
	   and v.file#=f.file#
	 order by f.ts#, f.relfile# 
   ;
   tag varchar2(80):=chr(10)||
     'Problem: Oversized File - See Note:107591.1 (Bug:568232 , Bug:925105)';
  begin
   For R in cBigFile
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OVERSIZED FILE ('||r.blocks||' blocks) TS='||R.TS||
			' RFILE='||r.RFILE||
			' ABS='||r.AFILE||' Name='||r.NAME);
     Fatal:=Fatal+1;
   End Loop;
  end;
 --
  function ObjectIsTemporary( o number ) return boolean 
  -- Return TRUE if object is a TEMPORARY object
  -- Return NULL if object does not exist
  -- Return FALSE if object is not temporary
  is
    Cursor cIsTemp is
	select bitand(nvl(flags,0), 2) IsTemp from obj$ where obj#=o
    ;
    ret boolean:=NULL;
  begin
    FOR R in cIsTemp LOOP -- For loop just to keep cursor closed
      if R.IsTemp=2 then ret:=TRUE; else ret:=FALSE; end if;
    END LOOP;
    return RET;
  end;
 --
  procedure OrphanedIndex is
   Cursor cOrphanInd is
	select i.obj#, i.dataobj#, i.ts#, i.file#, i.block#, i.bo#, s.type#
          from seg$ s, ind$ i
         where i.ts#=s.ts#(+)
           and i.file#=s.file#(+)
           and i.block#=s.block#(+)
           and i.dataobj# is not null   /* ie: A Physical object */
	   -- and not (i.file#=0 and i.block#=0) /* Covered by IF in loop */
	   and bitand(i.flags,4096)!=4096  /* Exclude NOSEGMENT index */
           and nvl(s.type#,0)!=6
        ;
   tag varchar2(80):=chr(10)||
    'Problem: Orphaned IND$ (no SEG$) - See Note:65987.1 (Bug:624613/3655873)';
  begin
   For R in cOrphanInd
   Loop
     if (ObjectIsTemporary(R.obj#)) then
	null; -- This is ok 
     else
       if (tag is not null) then hout.put_line(tag); tag:=null; end if;
       hout.put_line(' ORPHAN IND$: OBJ='||R.obj#||
			' DOBJ='||r.dataobj#||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' BO#='||r.bo#||' SegType='||R.type#);
       if (r.TS#=0 and r.file#=0 and r.block#=0) then
	hout.put_line(' ^- May be OK. Needs manual check');
       end if;
       Fatal:=Fatal+1;
     end if;
   End Loop;
  End;
 --
  function IndexIsNosegment( o number ) return boolean is
   Cursor cX is
	select bitand(flags,4096) noseg from ind$ where obj#=o;
   ret boolean:=null;
  begin
   For C in cX 
   loop
     if C.noseg=4096 then
	ret:=true;
     else
	ret:=false;
     end if;
   end loop;
   return ret;	/* true/false or NULL if not found */
  end;
 --
  procedure OrphanedIndexPartition is
   Cursor cOrphanInd is
	select i.obj#, i.ts#, i.file#, i.block#, i.bo#, s.type#
          from seg$ s, indpart$ i
         where i.ts#=s.ts#(+)
           and i.file#=s.file#(+)
           and i.block#=s.block#(+)
           and i.dataobj# is not null   /* ie: A Physical object */
	   and not (i.ts#=0 and i.file#=0 and i.block#=0) /* TEMP */
           and nvl(s.type#,0)!=6
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned Index Partition (no SEG$) - '||
     'See Note:65987.1 (Bug:624613)';
   noseg boolean:=null;
  begin
   For R in cOrphanInd
   Loop
     noseg:=IndexIsNosegment(R.bo#);
     if (noseg is null OR noseg = false) then
       if (tag is not null) then hout.put_line(tag); tag:=null; end if;
       hout.put_line(' ORPHAN INDPART$: OBJ='||R.obj#||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' BO#='||r.bo#||' SegType='||R.type#);
       Fatal:=Fatal+1;
     end if;
     if noseg is null then
       hout.put_line(' ^- INDPART$ . BO# has no IND$ entry ??');
     end if;
   End Loop;
  End;
 --
  procedure OrphanedIndexSubPartition is
   Cursor cOrphanInd is
	select i.obj#, i.ts#, i.file#, i.block#, i.pobj#, s.type#
          from seg$ s, indsubpart$ i
         where i.ts#=s.ts#(+)
           and i.file#=s.file#(+)
           and i.block#=s.block#(+)
           and i.dataobj# is not null   /* ie: A Physical object */
	   and not (i.ts#=0 and i.file#=0 and i.block#=0) /* TEMP */
           and nvl(s.type#,0)!=6
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned Index SubPartition (no SEG$) - '||
     'See xxxxx';
  begin
   For R in cOrphanInd
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' ORPHAN INDSUBPART$: OBJ='||R.obj#||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' POBJ#='||r.pobj#||' SegType='||R.type#);
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure OrphanedTable is
   Cursor cOrphanTab is
	select i.obj#, i.dataobj#, i.ts#, i.file#, i.block#, i.bobj#, s.type#,
		bitand(i.property,64) iot
          from seg$ s, tab$ i
         where i.ts#=s.ts#(+)
           and i.file#=s.file#(+)
           and i.block#=s.block#(+)
           and i.dataobj# is not null   /* ie: A Physical object */
	   /* and not (i.ts#=0 and i.file#=0 and i.block#=0) /* TEMP */
           and nvl(s.type#,0)!=5
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned TAB$ (no SEG$)';
  begin
   For R in cOrphanTab
   Loop
     if (ObjectIsTemporary(R.obj#)) then
	null; -- This is ok 
     else
       if (r.iot=64 and r.dataobj#=0 and r.ts#=0 and r.file#=0 and r.block#=0)
       then
	null; -- this is a truncated IOT - see 4701060
       else
        if (tag is not null) then hout.put_line(tag); tag:=null; end if;
        hout.put_line(' ORPHAN TAB$: OBJ='||R.obj#||
			' DOBJ='||r.dataobj#||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' BOBJ#='||r.bobj#||' SegType='||R.type#);
        if (r.TS#=0 and r.file#=0 and r.block#=0) then
 	 hout.put_line(' ^- May be OK. Needs manual check');
        end if;
        Fatal:=Fatal+1;
       end if;
     end if;
   End Loop;
  End;
 --
  procedure OrphanedTablePartition is
   Cursor cOrphanTab is
	select i.obj#, i.ts#, i.file#, i.block#, i.bo#, s.type#
          from seg$ s, tabpart$ i
         where i.ts#=s.ts#(+)
           and i.file#=s.file#(+)
           and i.block#=s.block#(+)
           and i.dataobj# is not null   /* ie: A Physical object */
	   and not (i.ts#=0 and i.file#=0 and i.block#=0) /* TEMP */
           and nvl(s.type#,0)!=5
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned Table Partition (no SEG$) - (Cause unknown)';
  begin
   For R in cOrphanTab
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' ORPHAN TABPART$: OBJ='||R.obj#||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' BO#='||r.bo#||' SegType='||R.type#);
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure OrphanedTableSubPartition is
   Cursor cOrphanTab is
	select i.obj#, i.ts#, i.file#, i.block#, i.pobj#, s.type#
          from seg$ s, tabsubpart$ i
         where i.ts#=s.ts#(+)
           and i.file#=s.file#(+)
           and i.block#=s.block#(+)
           and i.dataobj# is not null   /* ie: A Physical object */
	   and not (i.ts#=0 and i.file#=0 and i.block#=0) /* TEMP */
           and nvl(s.type#,0)!=5
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned Table SubPartition (no SEG$) - (Cause unknown)';
  begin
   For R in cOrphanTab
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' ORPHAN TABSUBPART$: OBJ='||R.obj#||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' POBJ#='||r.pobj#||' SegType='||R.type#);
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure OrphanedTabComPart is
   Cursor cOrphanTCP is
	select t.obj# , t.bo#, b.name, p.name pname, p.subname, b.owner#
          from tabcompart$ t, obj$ b, obj$ p
         where b.obj#(+)=t.bo# 
           and p.obj#(+)=t.obj# and p.obj#+b.obj# is null
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned TabComPart$ from OBJ$ - (see Bug:1528062)';
  begin
   For R in cOrphanTCP
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' ORPHAN TABCOMPART$: OBJ='||R.obj#||
			' OBJ#Name='||r.subname||' ('||r.pname||')'||
			' BO#='||R.bo#||
			' BO#name='||Owner(R.owner#)||'.'||R.name);
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure ZeroTabSubPart is
   Cursor cZero is
	select sp.obj#, sp.ts#, sp.pobj#, b.name, b.subname, b.owner#
          from indsubpart$ sp, obj$ b
         where sp.file#=0 and sp.block#=0
      	   and b.obj#(+)=sp.pobj#
        ;
   tag varchar2(80):=chr(10)||
     'Problem: IndSubPart$ has File#=0'||
     '(see Bug:1614155 if also Orphan SEG$)';
  begin
   For R in cZero
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' ORPHAN INDSUBPART$: OBJ#='||R.obj#||
			' POBJ#='||R.pobj#||
			' Index='||Owner(R.Owner#)||'.'||R.name||
			' Partn='||R.subname);
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure OrphanedSeg$ is
   Cursor cOrphanSeg is
	select 'TYPE2 UNDO' typ, s.ts#, s.file#, s.block#
          from seg$ s, undo$ u
         where s.ts#=u.ts#(+)
           and s.file#=u.file#(+)
           and s.block#=u.block#(+)
           and s.type#=10
	   -- and u.file# is null
	   and decode(u.status$,1,null,u.status$) is null
	UNION ALL
	select 'UNDO' typ, s.ts#, s.file#, s.block#
          from seg$ s, undo$ i
         where s.ts#=i.ts#(+)
           and s.file#=i.file#(+)
           and s.block#=i.block#(+)
           and s.type#=1
	   -- and i.file# is null
	   and decode(i.status$,1,null,i.status$) is null
	UNION ALL
	select 'DATA' typ, s.ts#, s.file#, s.block#
          from seg$ s, 
		(select a.ts#,a.file#,a.block# from tab$ a
		 union all 
		 select b.ts#,b.file#,b.block# from clu$ b
		 union all 
		 select c.ts#,c.file#,c.block# from tabpart$ c
		 union all 
		 select d.ts#,d.file#,d.block# from tabsubpart$ d
		) i
         where s.ts#=i.ts#(+)
           and s.file#=i.file#(+)
           and s.block#=i.block#(+)
           and s.type#=5
	   and i.file# is null
	UNION ALL
	select 'INDEX' typ, s.ts#, s.file#, s.block#
          from seg$ s, 
		(select a.ts#,a.file#,a.block# from ind$ a
		 union all 
		 select b.ts#,b.file#,b.block# from indpart$ b
		 union all 
		 select d.ts#,d.file#,d.block# from indsubpart$ d
		) i
         where s.ts#=i.ts#(+)
           and s.file#=i.file#(+)
           and s.block#=i.block#(+)
           and s.type#=6
	   and i.file# is null
	UNION ALL
	select 'LOB' typ, s.ts#, s.file#, s.block#
          from seg$ s, lob$ i
         where s.ts#=i.ts#(+)
           and s.file#=i.file#(+)
           and s.block#=i.block#(+)
           and s.type#=8
	   and i.file# is null
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned SEG$ Entry';
  begin
   For R in cOrphanSeg
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' ORPHAN SEG$: SegType='||R.typ||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#);
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure DictAt( ts number, fi number, bl number ) is
   Cursor cDictAt is
     select typ, ts#,file#,block#,count(*) CNT
      from (
	select 'UNDO$' typ, u.ts#, u.file#, u.block# from undo$ u
         where decode(u.status$,1,null,u.status$) is null
	UNION ALL
	select 'TAB$', a.ts#,a.file#,a.block# from tab$ a
	UNION ALL
	select 'CLU$', b.ts#,b.file#,b.block# from clu$ b
	UNION ALL
	select 'TABPART$', c.ts#,c.file#,c.block# from tabpart$ c
	UNION ALL
	select 'TABSUBPART$', d.ts#,d.file#,d.block# from tabsubpart$ d
	UNION ALL
	select 'IND$', a.ts#,a.file#,a.block# from ind$ a
	UNION ALL
	select 'INDPART$', b.ts#,b.file#,b.block# from indpart$ b
	UNION ALL
	select 'INDSUBPART$', d.ts#,d.file#,d.block# from indsubpart$ d
	UNION ALL
	select 'LOB$' , i.ts#, i.file#, i.block# from lob$ i
	UNION ALL
	select 'LOBFRAG$' , i.ts#, i.file#, i.block# from lobfrag$ i
--	UNION ALL
--	select 'RECYCLEBIN$' , i.ts#, i.file#, i.block# from recyclebin$ i
       ) 
       where ts#= TS and file# = FI and block#= BL
       group by typ, ts#,file#,block#
      ;
  begin
   For R in cDictAt
   Loop
     hout.put_line('^  '||R.typ||' has '||R.cnt||' rows');
   End Loop;
  End;
 --
  procedure DuplicateBlockUse is
   Cursor cDuplicateBlock is
     select ts#,file#,block#,count(*) CNT, min(typ) mintyp
      from (
	select 'UNDO$' typ, u.ts#, u.file#, u.block# from undo$ u
         where decode(u.status$,1,null,u.status$) is null
	UNION ALL
	select 'TAB$', a.ts#,a.file#,a.block# from tab$ a
	UNION ALL
	select 'CLU$', b.ts#,b.file#,b.block# from clu$ b
	UNION ALL
	select 'TABPART$', c.ts#,c.file#,c.block# from tabpart$ c
	UNION ALL
	select 'TABSUBPART$', d.ts#,d.file#,d.block# from tabsubpart$ d
	UNION ALL
	select 'IND$', a.ts#,a.file#,a.block# from ind$ a
	UNION ALL
	select 'INDPART$', b.ts#,b.file#,b.block# from indpart$ b
	UNION ALL
	select 'INDSUBPART$', d.ts#,d.file#,d.block# from indsubpart$ d
	UNION ALL
	select 'LOB$' , i.ts#, i.file#, i.block# from lob$ i
	UNION ALL
	select 'LOBFRAG$' , i.ts#, i.file#, i.block# from lobfrag$ i
--	UNION ALL
--	select 'RECYCLEBIN$' , i.ts#, i.file#, i.block# from recyclebin$ i
       ) 
       where block#!=0 
       group by ts#,file#,block#
       having count(*) > 1
	  and min(typ)!='CLU$' 	/* CLUSTER can have multiple entries */
      ;
   tag varchar2(80):=chr(10)||
     'Problem: Block has multiple dictionary entries';
  begin
   For R in cDuplicateBlock
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' MULTI DICT REF: TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' cnt='||R.cnt);
     DictAt(R.ts#, R.file#, R.block#);
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure OrphanedIndPartObj# is
   Cursor cOrphanInd is
	select i.obj#, i.ts#, i.file#, i.block#, i.bo#
          from obj$ o, indpart$ i
         where o.obj#(+)=i.obj# and o.obj# is null
        ;
   tag varchar2(80):=chr(10)||
     'Problem: Orphaned Index Partition Obj# (no OBJ$) - '||
     'See Bug:5040222';
   noseg boolean:=null;
  begin
   For R in cOrphanInd
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' ORPHAN INDPART$: OBJ#='||R.obj#||' - no OBJ$ row');
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure TruncatedCluster is
   Cursor cBadCluster is
   	select /*+ ORDERED */ 
		t.obj#, u.name owner, o.name, t.dataobj# td, c.dataobj# cd
	  from clu$ c, tab$ t, obj$ o, user$ u
	 where t.ts# = c.ts#
	   and   t.file# = c.file#
	   and   t.block# = c.block#
	   and   t.dataobj# != c.dataobj#
	   and   t.obj# = o. obj#
	   and   o.owner# = u.user#
   ;
   tag varchar2(80):=chr(10)||
     'Problem: Clustered Tables with bad DATAOBJ# - '||
     'See Note:109134.1 (Bug:1283521)';
  begin
   For R in cBadCluster
   Loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' Bad TAB$ entry: TAB OBJ='||R.obj#||
			' NAME='||r.owner||'.'||r.name||
			' Tab DOBJ='||r.td||' != '||r.cd
     );
     Fatal:=Fatal+1;
   End Loop;
  End;
 --
  procedure HighObjectIds is
   Cursor cHighObject is
	select max(obj#) maxobj, max(dataobj#) maxdobj from obj$
   ;
   tag varchar2(80):=chr(10)||
     'Problem: High Objects IDS exist - See Note:76746.1 (Bug:970640)';
  begin
   For R in cHighObject
   Loop
     if (r.maxobj>2000000000) or (r.maxdobj>2000000000) then
       hout.put_line(tag); 
       hout.put_line(' HIGH OBJECT NUMBERS EXIST: max(OBJ)='||r.maxobj||
			' max(dataobj#)='||r.maxdobj);
       Fatal:=Fatal+1;
     end if;
   End Loop;
  End;
 --
  procedure PQsequence is
   Cursor cPQ is
	SELECT max_value, cycle_flag, last_number
          FROM DBA_SEQUENCES
         WHERE sequence_owner='SYS' and sequence_name='ORA_TQ_BASE$'
   ;
   tag varchar2(80):=chr(10)||
     'Problem: PQ Sequence needs fixing - See Note:66450.1 (Bug:725220)';
  begin
   For R in cPQ
   Loop
     if (R.cycle_flag!='Y' and R.last_number>1000000) then
       if (tag is not null) then hout.put_line(tag); tag:=null; end if;
       hout.put_line(' ORA_TQ_BASE$ is not CYCLIC - '||
			R.max_value-R.last_number||' values left');
       Fatal:=Fatal+1;
     end if;
   End Loop;
  End;
 --
  procedure PoorDefaultStorage is
   Cursor cPoorStorage is
	select * from dba_tablespaces
	 where (initial_extent<1024*1024 or contents='TEMPORARY')
	   and next_extent<65536 
	   and min_extlen<65536 
	   and pct_increase<5 
	   and max_extents>3000
	;
   tag varchar2(80):=chr(10)||
		'Warning: Poor Default Storage Clauses (see Note:50380.1)';
  begin
   For R in cPoorStorage
   Loop
     if (tag is not null) then 
	hout.put_line(tag); tag:=null; 
	hout.put_line('  '||rpad('Tablespace',30)||rpad('Init',10)||
		rpad('Next',10)||rpad('Min',10)||rpad('Pct',4)||
		'MaxExtents'
	); tag:=null; 
     end if;
     hout.put_line('  '||rpad(R.tablespace_name,30)
			||rpad(r.initial_extent,10)
			||rpad(r.next_extent,10)
			||rpad(r.min_extlen,10)
			||rpad(r.pct_increase,4)
			||r.max_extents );
     Warn:=Warn+1;
   End Loop;
  End;
 --
  procedure PoorStorage is
   Cursor cPoorStorage is
	select * from dba_segments
	 where (initial_extent<65535
	   and next_extent<65536 
	   and pct_increase<5 
	   and max_extents>3000
	   and extents>500)
	  or extents>3000
	;
   tag varchar2(80):=chr(10)||
	'Warning: Poor Storage Clauses (see Note:50380.1)';
  begin
   For R in cPoorStorage
   Loop
     if (tag is not null) then 
	hout.put_line(tag); 
	tag:=null; 
	hout.put_line('  '||rpad('Segment',50)||rpad('Next',10)||
		rpad('Exts',7)||rpad('Pct',4)||
		'MaxExtents'
	);
     end if;
     hout.put_line('  '||
	rpad(R.segment_type||' '||R.owner||'.'||R.segment_name,50)
			||rpad(r.next_extent,10)
			||rpad(r.extents,7)
			||rpad(r.pct_increase,4)
			||r.max_extents );
     Warn:=Warn+1;
   End Loop;
  End;
 --
  procedure FetUet(ts number, fil number, len number) is
   Cursor cMap(ts number,fil number) is
     select block#,length,'FET$' typ 
	 from fet$ where ts#=TS and file#=FIL
      UNION ALL
     select block#,length,'UET$' typ 
	 from uet$ where ts#=TS and file#=FIL
     order by 1
   ;
   BlkExpected number;
   prev cMap%Rowtype;
   tag varchar2(80):=chr(10)||
     'Problem: Fet/Uet corruption in TS#='||TS||' RFile='||FIL;
  begin
   BlkExpected:=2;
   For R in cMap(TS,FIL)
   Loop
     if (R.block#!=BlkExpected) then 
        if (tag is not null) then hout.put_line(tag); tag:=null; end if;
       	if R.block# 0;
   tag varchar2(80):=chr(10)||
     'Problem:  NULL SYS_EVTS/NTTRIGCOL - Bug:1362374 / Note:131528.1';
  Begin
   for R in CTrig
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' TRIGGER$ has '||R.cnt||' NULL entries');
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure TinyFiles is
   Cursor ctiny is 
	select file#, ts#, blocks from file$
	 where status$=2 and blocks<=1;
   tag varchar2(80):=chr(10)||
     'Problem:  Tiny File size in FILE$ - Bug:1646512 ?';
  Begin
   for R in CTiny
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' FILE$ FILE#='||R.file#||' has BLOCKS='||R.blocks);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure bug1365707_badobj is
   Cursor cBad is 
	select obj#, type#, name from obj$
	 where type#=0 and name!='_NEXT_OBJECT';
   tag varchar2(80):=chr(10)||
     'Problem:  Bad OBJ$ entry with TYPE#=0 - see Bug:1365707';
  Begin
   for R in CBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ$ OBJ#='||R.OBJ#||' TYPE#=0 NAME='||R.name);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure bug1842429_badview is
   Cursor cBad is 
	select o.obj#, owner#, name from obj$ o, view$ v
	 where o.type#=4 
	   and o.oid$ is not null
	   and v.obj#=o.obj#
           and bitand(v.property,1)=0
   ;
   tag varchar2(80):=chr(10)||
     'Problem:  OBJ$.OID$ set for a VIEW - see Note:157540.1 / Bug:1842429';
  Begin
   for R in CBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ$ OBJ#='||R.OBJ#||' Owner='||Owner(R.owner#)||
		    ' NAME='||R.name);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure TabPartCountMismatch is
   Cursor cPartObj is 
	select o.obj#, o.owner#, o.name, 
		t.property, p.partcnt, bitand(p.spare2,255) comp
	  from obj$ o, tab$ t, partobj$ p
	 where o.type#=2 		/* table */
	   and o.dataobj# is null 
	   and o.obj#=t.obj#
	   and bitand(t.property,32)=32	/* partitioned table */
	   and o.obj#=p.obj#(+)
   ;
   tag varchar2(80):=chr(10)||'Problem:  OBJ$-PARTOBJ$-50;
   tag varchar2(80):=chr(10)||
	'Problem:  Sequence IDGEN1$ INCREMENT_BY too high - Bug:1375026';
  Begin
   for R in cSeq
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure Bug1359472BadOwner is
   Cursor cBad is
	select obj#, type#, owner#, name from obj$ 
	 where owner# not in (select user# from user$) 
	   and type# != 10; 
   tag varchar2(80):=chr(10)||
	'Problem:  OBJ$.OWNER# not in USER$ - See Bug:1359472';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ$ OBJ#='||R.OBJ#||' TYPE='||R.type#||' NAME='||R.name
		||' Owner#='||R.OWNER#);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure Bug2371453synonym is
   Cursor cBad is
     select distinct o.obj#, o.owner#, o.name 
       from obj$ o , idl_ub1$ i
      where type#=5
        and ctime!=mtime
        and i.obj#=o.obj#	/* Has IDL information */
     ;
   tag varchar2(80):=chr(10)||
	'Warning:  Synonym LAST_DDL_TIME!=CREATED - May hit Bug:2371453';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ#='||R.OBJ#||' Name='||Owner(R.owner#)||'.'||R.name);
     Warn:=Warn+1;
   end loop;
  End;
 --
  procedure ObjSynMissing is
   Cursor cBad is
     select o.obj#, o.owner#, o.name 
       from obj$ o , syn$ s
      where o.type#=5
        and o.obj#=s.obj#(+)	
	and o.linkname is null /* Not a remote object */
	and s.obj# is null
     ;
   tag varchar2(80):=chr(10)||
	'Warning:  SYN$ entry missing for OBJ$ type#=5';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ#='||R.OBJ#||' Name='||Owner(R.owner#)||'.'||R.name);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure CheckDual is
   Cursor cBad is
     select dummy from dual;
   tag varchar2(80):=chr(10)||
	'Fatal:  DUAL is unusual';
   n number:=0;
  Begin
   for R in cBad
   loop
     n:=n+1;
     if (n>1) then
       hout.put_line(chr(10)||'DUAL has more than one row');
       Fatal:=Fatal+1;
       exit;
     end if;
     if (nvl(R.dummy,'Z')!='X') then
       hout.put_line(chr(10)||'DUAL . DUMMY does not contain "X"');
       Fatal:=Fatal+1;
     end if;
   end loop;
  End;
 --
  procedure Bug2728624badOid is
   Cursor cBad is
	select o.obj# , o.type#, o.owner#, o.name, c.col#, c.intcol#, 
		c.name cname, t.property
	  from obj$ o, col$ c, coltype$ ct, oid$ oi, tab$ t
	 where o.obj#=ct.obj#
	   and ct.obj#=c.obj#
	   and ct.col#=c.col#
	   and ct.intcol#=c.intcol#
	   and oi.oid$(+)=ct.toid
	   and oi.oid$ is null
	   and o.obj#=t.obj#(+)
	  ;
   tag varchar2(80):=chr(10)||
	'Problem:  Column type is OBJECT with missing OID$ - Bug:2728624 ?';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ#='||R.OBJ#||' Name='||Owner(R.owner#)||'.'||R.name||
		' IntCol#='||R.intcol#||'='||R.cname||' TabProp='||R.property);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure Bug3532977source is
   Cursor cBad is
	select count(*) cnt, count(distinct s.obj#) nobj 
	  from source$ s, obj$ o
	 where s.obj#=o.obj#(+)
	  and o.obj# is null
	 having count(*) > 0
	  ;
   tag varchar2(80):=chr(10)||
	'Problem:  SOURCE$ for OBJ# not in OBJ$ - Bug:3532977 ?';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' SOURCE$ has '||R.cnt||
	' rows for '||R.nobj||' OBJ# values not in OBJ$');
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure Bug2405258lob is
   Cursor cBad is
	select l.obj#, l.lobj#
	  from lob$ l, obj$ o
	 where l.lobj#=o.obj#(+)
	  and o.obj# is null
	 ;
   tag varchar2(80):=chr(10)||
	'Problem:  LOB$ . LOBJ# not in OBJ$ - Bug:2405258 ?';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' LOB$.LOBJ# has no OBJ$ entry for LOBJ#='||R.lobj#||
	' (OBJ#='||R.obj#||')');
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure Bug3753873indpname is
   Cursor cBad is
	select io.obj# io, io.name ionam, ipo.obj# ipo, ipo.name iponam
	 from obj$ io , indpart$ ip,  obj$ ipo
	where ipo.type#=20  /* IND PART */
	  and ip.obj#=ipo.obj#
	  and io.obj#(+)=ip.bo#
	  and nvl(io.name,'"')!=ipo.name
	;
   tag varchar2(80):=chr(10)||
    'Problem:  OBJ$.NAME mismatch for INDEX v INDEX PARTITION - Bug:3753873 ?';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' Ind Part OBJ$.OBJ# '||R.ipo||' '||R.iponam||
		'!='||R.ionam||' OBJ#='||R.io);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure BadCboHiLo is
   Cursor cBad is
	select obj#,intcol#,lowval,hival
	 from hist_head$ where lowval>hival
	;
   tag varchar2(80):=chr(10)||'Problem:  HIST_HEAD$.LOWVAL > HIVAL !';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ# '||R.obj#||' INTCOL#='||R.intcol#);
     Warn:=Warn+1;
   end loop;
  End;
 --
  procedure ObjectNames is
   Cursor cBad is
	select username, object_type, 
		substr(owner||'.'||object_name,1,62) Name
  	  from dba_objects, dba_users
         where object_name=username
   	   and (owner=username OR owner='PUBLIC')
	;
   tag varchar2(80):=chr(10)||
	'Warning:  OBJECT name clashes with SCHEMA name - Bug:2894111 etc..';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' Schema='||R.username||' Object='||R.name||' ('||
		R.object_type||')');
     Warn:=Warn+1;
   end loop;
  End;
 --
  procedure BadDepends is
   Cursor cBad is
	select do.obj# dobj, po.obj# pobj , p_timestamp, po.stime p_stime 
  	  from sys.obj$ do, sys.dependency$ d,  sys.obj$ po
	 where P_OBJ#=po.obj#(+) 
	   and D_OBJ#=do.obj#
	   and do.status=1 /*dependent is valid*/
	   and po.status=1 /*parent is valid*/
	   and po.stime!=p_timestamp /*parent timestamp not match*/
	  order by 2,1
	;
   tag varchar2(80):=chr(10)||
	'Problem:  Dependency$ p_timestamp mismatch for VALID objects';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' P_OBJ#='||R.pobj||' D_OBJ#='||R.dobj);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure BadCol# is
   Cursor cBad is
	select o.obj# , max(intcol#) maxi, max(col#) maxc
  	  from sys.col$ o
         group by o.obj#
        having max(intcol#)>1000 or max(col#)>999
	;
   tag varchar2(80):=chr(10)||
	'Problem:  COL$ intcol#/col# too high (bug 2212987)';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ#='||R.obj#||' max(intcol#)'||R.maxi||
		' max(col#)='||R.maxc);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure ChkIotTS is
   Cursor cBad is
	select o.owner#, o.obj# , o.name , t.ts#, t.file#, t.block#
  	  from sys.obj$ o, sys.tab$ t
	 where bitand(t.property,64)=64	/* Marked as an IOT */
	   and ts#!=0
	   and o.obj#=t.obj#
	;
   tag varchar2(80):=chr(10)||
	'Problem:  IOT tab$ has TS#!=0 ?? May be OK - needs checking';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(
	' OBJ#='||R.obj#||' ('||Owner(R.owner#)||'.'||r.name||') '||
		' TS#='||R.ts#||' f='||R.file#||' b='||r.block#
     );
     Warn:=Warn+1;
   end loop;
  End;
 --
  procedure DuplicateDataobj is
   Cursor cBad is
	select * from 
	(
	select /*+ NO_MERGE */ many.dataobj#, o.obj#, o.owner#,
		o.name, o.subname,
		o.type#, t.property
	  from ( select dataobj# from obj$
		  where dataobj# is not null
	          group by dataobj#
	         having count(*)>1) many, obj$ o, tab$ t
	 where many.dataobj# = o.dataobj#(+)
	   and o.type#(+)!=3				/* Not a cluster */
	   and t.obj#(+)=o.obj#
	) 
	where bitand(property, 1024)!=1024	/* Not a cluster table */
	  or property is null
	order by dataobj#, obj#
   ;
   tag varchar2(80):=chr(10)||
	'Problem:  Duplicate DATAOBJ# (may be valid if using transported TS)';
   sub boolean:=false;
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' DATAOBJ#='||R.DATAOBJ#||' OBJ#='||R.obj#||
	' Name='||Owner(R.owner#)||'.'||R.name||' '||R.subname||
		' Type#='||R.type#);
     if (R.type#=34 /*table subpart*/) then
	sub:=true;
     end if;
     Fatal:=Fatal+1;
   end loop;
   if sub then
     hout.put_line(' Subpartition duplicates could be caused by bug:2597763');
   end if;
  End;
 --
  procedure ObjSeqMissing is
   Cursor cBad is
     select o.obj#, o.owner#, o.name 
       from obj$ o , seq$ s
      where o.type#=6
        and o.obj#=s.obj#(+)	
	and o.linkname is null	/* Not remote */
	and s.obj# is null
     ;
   tag varchar2(80):=chr(10)||
	'Warning:  SEQ$ entry missing for OBJ$ type#=6';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ#='||R.OBJ#||' Name='||Owner(R.owner#)||'.'||R.name);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure badIcolDepCnt is
   Cursor cBad is
	select i.obj# , nvl(i.spare1,i.intcols) expect, ic.cnt got
	  from ind$ i,
	   (select obj#, count(*) cnt from icoldep$ group by obj# ) ic
	 where ic.obj#=i.obj#
	  and ic.cnt!=nvl(i.spare1,i.intcols)
	;
   tag varchar2(80):=chr(10)||
    'Error:  ICOLDEP$ count!=IND$ expected num dependencies - bug 5217913';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' OBJ#='||R.OBJ#||' '||ObjName(R.obj#)||
	' IND$ expects '||R.expect||' ICOLDEP$ has '||R.got);
     Fatal:=Fatal+1;
   end loop;
  End;
 --
  procedure warnIcoldep is
   Cursor cBad is
	select adt.obj#,adt.icobj#, adt.intcol#,adt.name
	from
	( select c.obj#, ic.obj# icobj#, c.intcol#, c.name
	 from col$ c , icoldep$ ic
	 where c.type#=121 /*index on ADT*/
	  and c.obj#=ic.bo#
	  and c.intcol#=ic.intcol#
	) adt,
	(select c.obj#, c.intcol#, c.name , ic.obj# icobj#
	  from col$ c , icoldep$ ic
	  where bitand(c.property,33)=33        /* index on ADT attribute */
	   and c.obj#=ic.bo#
	   and c.intcol#=ic.intcol#
	) adtattr
	where adt.obj#=adtattr.obj#             /* same table */
	  and adt.icobj#=adtattr.icobj#         /* same index */
	  and adt.intcol#+1 = adtattr.intcol#   /* likely same ADT/attr */
	order by 1,2
	;
   tag varchar2(80):=chr(10)||
	'Warning:  ICOLDEP$ may reference ADT and its attributes'||
	' - see bug 5217913';
  Begin
   for R in cBad
   loop
     if (tag is not null) then hout.put_line(tag); tag:=null; end if;
     hout.put_line(' Index OBJ#='||R.ICOBJ#||' '||ObjName(R.icobj#)||
	' intcol#='||R.intcol#||'='||R.name);
     Warn:=Warn+1;
   end loop;
  End;
 --
  procedure NosegmentIndex is
   Cursor cWarn is
	select i.obj#, i.dataobj#, i.ts#, i.file#, i.block#, i.bo#, s.type#
          from seg$ s, ind$ i
         where i.ts#=s.ts#(+)
           and i.file#=s.file#(+)
           and i.block#=s.block#(+)
	   and bitand(i.flags,4096)=4096  /* Exclude NOSEGMENT index */
        ;
   tag varchar2(80):=chr(10)||
    'Warning: NOSEGMENT IND$ exists (these are allowed but care needed)';
   --
   Procedure CheckIndPart( o number ) is
    Cursor Cchk is
	select  i.obj#, i.dataobj#, i.ts#, i.file#, i.block#
          from indpart$ i 
	 where i.bo#=o
	   and (i.file#!=0 OR i.block#!=0);
   begin
    For R in Cchk Loop
     hout.put_line(' ^- PROBLEM: Child INDPART$ with FILE/BLK (bug 4683380)');
     hout.put_line(' ^- ( OBJ='||R.obj#|| ' DOBJ='||r.dataobj#||
		' TS='||r.TS#||
		' RFILE/BLOCK='||r.file#||' '||r.block#||')' );
     Fatal:=Fatal+1;
    end loop;
   end;
  begin
   For R in cWarn
   Loop
       if (tag is not null) then hout.put_line(tag); tag:=null; end if;
       hout.put_line(' NOSEGMENT IND$: OBJ='||R.obj#||
			' DOBJ='||r.dataobj#||
			' TS='||r.TS#||
			' RFILE/BLOCK='||r.file#||' '||r.block#||
			' BO#='||r.bo#||' SegType='||R.type#);
       if (R.type# is not null) then
	hout.put_line(' ^- PROBLEM: NOSEGMENT Index has a segment attached ?');
        Fatal:=Fatal+1;
       else
        Warn:=Warn+1;
       end if;
       if (r.TS#!=0 or r.file#!=0 or r.block#!=0) then
	hout.put_line(' ^- Index has ts#/file#/block# set ???');
       end if;
       CheckIndPart(R.obj#);
   End Loop;
  End;
 --
  procedure Full is
  begin
        hout.put_line('HCheck Version '||Ver);
       --
	OversizedFiles;
	TinyFiles;
  	TabPartCountMismatch;
  	bug1360714_Composite;
        OrphanedTabComPart;
  	ZeroTabSubPart;
  	MissingSum$;
	DuplicateDataobj;
  	ObjSynMissing ;
  	ObjSeqMissing ;
       -- 
	OrphanedIndex;
	OrphanedIndexPartition;
	OrphanedIndexSubPartition;
	OrphanedTable;
	OrphanedTablePartition;
	OrphanedTableSubPartition;
	OrphanedSeg$;
        OrphanedIndPartObj#;
	DuplicateBlockUse;
	HighObjectIds;
	PQsequence;
	TruncatedCluster;
	FetUet;
  	Uet0Check;
	ExtentlessSeg;
	SeglessUET;
  	BadInd$;
  	BadTab$;
	BadIcolDepCnt;
	warnIcolDep;
  	OnlineRebuild$;
	bug1584155;
  	bug1362374_trigger;
  	bug1365707_badobj;
  	bug1842429_badview;
	Bug1375026Seq;
  	Bug1805146DroppedFuncIdx;
  	Bug1359472BadOwner;
	Bug2728624badOid;
	Bug3532977source;
	Bug2405258lob;
	Bug3753873indpname;
       --
  	BadPublicObjects;
  	BadSegFreelist;
	BadCol#;
	BadDepends;
       --
        CheckDual;
  	Bug2371453synonym;
  	PoorDefaultStorage;
  	PoorStorage;
	ObjectNames;
  	BadCboHiLo;
	ChkIotTs;
	NoSegmentIndex;
       --
        hout.new_line;
        hout.put_line('Found '||Fatal||' potential problems and '||
		warn||' warnings');
        hout.new_line;
        if (Fatal>0 or Warn>0) then
          hout.put_line('Contact Oracle Support with the output');
          hout.put_line('to check if the above needs attention or not');
   	end if;
  end;
end hcheck;
/
show errors
REM
set serverout on
execute hcheck.full
REM ======================================================================
After creating the 2 packages you must login as sysdba from sqlplus and run
sqlplus>set serveroutput on
sqlplus>execute hcheck.full
This will create the trace file output at the udump directory. An example follows
SQL> set serveroutput on
SQL> execute hcheck.full
HCheck Version 8i/1.30

Problem:  Duplicate DATAOBJ# (may be valid if using transported TS)
DATAOBJ#=0 OBJ#=662756 Name=JTF.DR$JTF_TASKS_TL_IM$K  Type#=2
DATAOBJ#=0 OBJ#=662791 Name=JTF.DR$JTF_TASKS_TL_IM$N  Type#=2
DATAOBJ#=0 OBJ#=798928 Name=JTF.DR$JTF_NOTES_TL_C1$K  Type#=2
DATAOBJ#=0 OBJ#=798933 Name=JTF.DR$JTF_NOTES_TL_C1$N  Type#=2

Problem: Orphaned IND$ (no SEG$) - See Note:65987.1 (Bug:624613/3655873)
ORPHAN IND$: OBJ=830178 DOBJ=830178 TS=0 RFILE/BLOCK=0 0 BO#=830174 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=830176 DOBJ=830176 TS=0 RFILE/BLOCK=0 0 BO#=830174 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=830193 DOBJ=830193 TS=0 RFILE/BLOCK=0 0 BO#=830191 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=830201 DOBJ=830201 TS=0 RFILE/BLOCK=0 0 BO#=830199 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=537290 DOBJ=537290 TS=0 RFILE/BLOCK=0 0 BO#=537288 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=342801 DOBJ=342801 TS=0 RFILE/BLOCK=0 0 BO#=342799 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=299283 DOBJ=299283 TS=0 RFILE/BLOCK=0 0 BO#=299281 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=537280 DOBJ=537280 TS=0 RFILE/BLOCK=0 0 BO#=537278 SegType=
^- May be OK. Needs manual check
SEG$ has no UET$ entry: TS#=3 RFILE#=4 BLK#=2383 TYPE#=9
SEG$ has no UET$ entry: TS#=5 RFILE#=46 BLK#=21520 TYPE#=9
SEG$ has no UET$ entry: TS#=6 RFILE#=45 BLK#=11189 TYPE#=9
SEG$ has no UET$ entry: TS#=7 RFILE#=22 BLK#=9677 TYPE#=9
NB: TYPE#=9 is special and may be OK
Other links
Change ownership for objects(update the dictionary!!)
Press CTRL-C during drop of functional based index
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search