8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

hcheck.sql : A Quick Check for Known Data Dictionary Problems

The "hckeck.sql" script checks for known data dictionary problems in the Oracle database, so it can be used as a quick health check of a database when trying to diagnose problems.

Download hcheck.sql

Read the contents of the following My Oracle Support (MOS) note.

Download the relevant script for your version from the MOS note. In this case we used "hcheck.sql" for version 10.2.05 onward.

Run hcheck.sql

Once you have the script all you need to do is connect as a user with SYSDBA privilege and run the script. In the case of multitenant architecture you will need to run the script for the root container and all PDBs, as shown below.

CONN sys@cdb1 AS SYSDBA
@hcheck.sql

CONN sys@pdb1 AS SYSDBA
@hcheck.sql

CONN sys@pdb2 AS SYSDBA
@hcheck.sql

The following example shows the output from a 12.2 PDB, so you will know what to expect.

SQL> @hcheck.sql
HCheck Version 07MAY18 on 16-JUN-2018 14:54:12
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name: CDB1
Is CDB?: YES CON_ID: 3 Container: PDB1

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp      Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- LobNotInObj                 ... 1202000100 <=  *All Rel* 06/16 14:54:12 PASS
.- MissingOIDOnObjCol          ... 1202000100 <=  *All Rel* 06/16 14:54:13 PASS
.- SourceNotInObj              ... 1202000100 <=  *All Rel* 06/16 14:54:20 PASS
.- OversizedFiles              ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- PoorDefaultStorage          ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- PoorStorage                 ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- TabPartCountMismatch        ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- OrphanedTabComPart          ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- MissingSum$                 ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- MissingDir$                 ... 1202000100 <=  *All Rel* 06/16 14:54:25 PASS
.- DuplicateDataobj            ... 1202000100 <=  *All Rel* 06/16 14:54:25 PASS
.- ObjSynMissing               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- ObjSeqMissing               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedUndo                ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndex               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndexPartition      ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndexSubPartition   ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedTable               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedTablePartition      ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedTableSubPartition   ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- MissingPartCol              ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedSeg$                ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndPartObj#         ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- DuplicateBlockUse           ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- FetUet                      ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- Uet0Check                   ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- SeglessUET                  ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- BadInd$                     ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- BadTab$                     ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- BadIcolDepCnt               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- ObjIndDobj                  ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- TrgAfterUpgrade             ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- ObjType0                    ... 1202000100 <=  *All Rel* 06/16 14:54:29 PASS
.- BadOwner                    ... 1202000100 <=  *All Rel* 06/16 14:54:29 PASS
.- StmtAuditOnCommit           ... 1202000100 <=  *All Rel* 06/16 14:54:29 PASS
.- BadPublicObjects            ... 1202000100 <=  *All Rel* 06/16 14:54:30 PASS
.- BadSegFreelist              ... 1202000100 <=  *All Rel* 06/16 14:54:30 PASS
.- BadDepends                  ... 1202000100 <=  *All Rel* 06/16 14:54:30 PASS
.- CheckDual                   ... 1202000100 <=  *All Rel* 06/16 14:54:31 PASS
.- ObjectNames                 ... 1202000100 <=  *All Rel* 06/16 14:54:31 PASS
.- BadCboHiLo                  ... 1202000100 <=  *All Rel* 06/16 14:54:32 PASS
.- ChkIotTs                    ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- NoSegmentIndex              ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- BadNextObject               ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- DroppedROTS                 ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- FilBlkZero                  ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- DbmsSchemaCopy              ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- OrphanedIdnseqObj           ... 1202000100 >  1201000000 06/16 14:54:33 PASS
.- OrphanedIdnseqSeq           ... 1202000100 >  1201000000 06/16 14:54:33 PASS
.- OrphanedObjError            ... 1202000100 >  1102000000 06/16 14:54:34 PASS
.- ObjNotLob                   ... 1202000100 <=  *All Rel* 06/16 14:54:34 PASS
.- MaxControlfSeq              ... 1202000100 <=  *All Rel* 06/16 14:54:34 PASS
.- SegNotInDeferredStg         ... 1202000100 >  1102000000 06/16 14:54:34 PASS
.- SystemNotRfile1             ... 1202000100 >   902000000 06/16 14:54:35 PASS
.- DictOwnNonDefaultSYSTEM     ... 1202000100 <=  *All Rel* 06/16 14:54:37 PASS
.- OrphanTrigger               ... 1202000100 <=  *All Rel* 06/16 14:54:37 PASS
.- ObjNotTrigger               ... 1202000100 <=  *All Rel* 06/16 14:54:37 PASS
---------------------------------------
16-JUN-2018 14:54:37  Elapsed: 25 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)



PL/SQL procedure successfully completed.

not supported.

Complete output is in trace file:
not supported.

SQL>

If you get any failures you can investigate and fix them.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.