VMCD.ORG » “hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g

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

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 size unlimited
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 attachments under SCRIPT to Create package hcheck, this is hcheck2.sql and/or hcheck3.sql
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 9i onwards (release 2.00 includes 8.1)
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.

eg:

SQL> exec hcheck.full
H.Check Version 9i+/hc3.45
—————————————
Catalog Version 11.2.0.3.0 (1102000300)
—————————————

Catalog Fixed
Procedure Name Version Vs Release Run
—————————— … ———- — ———- —
.- SynLastDDLTim … 1102000300 > 1001000200 : n/a
.- LobNotInObj … 1102000300 > 1000000200 : n/a
.- MissingOIDOnObjCol … 1102000300 <= *All Rel* : Ok .- SourceNotInObj ... 1102000300 > 1002000100 : n/a
.- IndIndparMismatch … 1102000300 > 1102000100 : n/a
.- InvCorrAudit … 1102000300 > 1102000100 : n/a
.- OversizedFiles … 1102000300 <= *All Rel* : Ok .- TinyFiles ... 1102000300 > 900010000 : n/a
.- PoorDefaultStorage … 1102000300 <= *All Rel* : Ok .- PoorStorage ... 1102000300 <= *All Rel* : Ok .- MissTabSubPart ... 1102000300 > 900010000 : n/a
.- PartSubPartMismatch … 1102000300 > 1102000100 : n/a
.- TabPartCountMismatch … 1102000300 <= *All Rel* : Ok .- OrphanedTabComPart ... 1102000300 > 900010000 : n/a
.- ZeroTabSubPart … 1102000300 > 902000100 : n/a
.- MissingSum$ … 1102000300 <= *All Rel* : Ok .- MissingDir$ ... 1102000300 <= *All Rel* : Ok .- DuplicateDataobj ... 1102000300 <= *All Rel* : Ok .- ObjSynMissing ... 1102000300 <= *All Rel* : Ok .- ObjSeqMissing ... 1102000300 <= *All Rel* : Ok .- OrphanedUndo ... 1102000300 <= *All Rel* : Ok .- OrphanedIndex ... 1102000300 <= *All Rel* : Ok .- OrphanedIndexPartition ... 1102000300 <= *All Rel* : Ok .- OrphanedIndexSubPartition ... 1102000300 <= *All Rel* : Ok .- OrphanedTable ... 1102000300 <= *All Rel* : Ok .- OrphanedTablePartition ... 1102000300 <= *All Rel* : Ok .- OrphanedTableSubPartition ... 1102000300 <= *All Rel* : Ok .- MissingPartCol ... 1102000300 <= *All Rel* : Ok .- OrphanedSeg$ ... 1102000300 <= *All Rel* : Ok .- OrphanedIndPartObj# ... 1102000300 > 1101000600 : n/a
.- DuplicateBlockUse … 1102000300 <= *All Rel* : Ok .- HighObjectIds ... 1102000300 > 801060000 : n/a
.- PQsequence … 1102000300 > 800060000 : n/a
.- TruncatedCluster … 1102000300 > 801070000 : n/a
.- FetUet … 1102000300 <= *All Rel* : Ok .- Uet0Check ... 1102000300 <= *All Rel* : Ok .- ExtentlessSeg ... 1102000300 <= *All Rel* : Ok .- SeglessUET ... 1102000300 <= *All Rel* : Ok .- BadInd$ ... 1102000300 <= *All Rel* : Ok .- BadTab$ ... 1102000300 <= *All Rel* : Ok .- BadIcolDepCnt ... 1102000300 > 1101000700 : n/a
.- WarnIcolDep … 1102000300 > 1101000700 : n/a
.- OnlineRebuild$ … 1102000300 <= *All Rel* : Ok .- DropForceType ... 1102000300 > 1001000200 : n/a
.- TrgAfterUpgrade … 1102000300 <= *All Rel* : Ok .- FailedInitJVMRun ... 1102000300 <= *All Rel* : Ok .- TypeReusedAfterDrop ... 1102000300 > 900010000 : n/a
.- Idgen1$TTS … 1102000300 > 900010000 : n/a
.- DroppedFuncIdx … 1102000300 > 902000100 : n/a
.- BadOwner … 1102000300 > 900010000 : n/a
.- UpgCheckc0801070 … 1102000300 <= *All Rel* : Ok .- BadPublicObjects ... 1102000300 <= *All Rel* : Ok .- BadSegFreelist ... 1102000300 <= *All Rel* : Ok .- BadCol# ... 1102000300 > 1001000200 : n/a
.- BadDepends … 1102000300 <= *All Rel* : Ok .- CheckDual ... 1102000300 <= *All Rel* : Ok .- ObjectNames ... 1102000300 <= *All Rel* : Ok .- BadCboHiLo ... 1102000300 <= *All Rel* : Ok .- ChkIotTs ... 1102000300 <= *All Rel* : Ok .- NoSegmentIndex ... 1102000300 <= *All Rel* : Ok .- BadNextObject ... 1102000300 <= *All Rel* : Ok .- OrphanIndopt ... 1102000300 > 902000800 : n/a
.- UpgFlgBitTmp … 1102000300 > 1001000100 : n/a
.- RenCharView … 1102000300 > 1001000100 : n/a
.- Upg9iTab$ … 1102000300 > 902000400 : n/a
.- Upg9iTsInd … 1102000300 > 902000500 : n/a
.- Upg10gInd$ … 1102000300 > 1002000000 : n/a
.- DroppedROTS … 1102000300 <= *All Rel* : Ok .- ChrLenSmtcs ... 1102000300 > 1101000600 : n/a
.- FilBlkZero … 1102000300 <= *All Rel* : Ok .- DbmsSchemaCopy ... 1102000300 <= *All Rel* : Ok Found 0 potential problem(s) and 0 warning(s) PL/SQL procedure successfully completed.