8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related articles.
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:
- hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c (Doc ID 136697.1)
- DBMS_HCHECK in Oracle Database 23c
Hope this helps. Regards Tim...