8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_HCHECK in Oracle Database 23c
In Oracle 23c the DBMS_HCHECK package allows us to check for known data dictionary problems in the database.
Several years ago Oracle released the hcheck.sql script (Doc ID 136697.1) to check for known data dictionary problems in the database. The DBMS_HCHECK packages means we no longer have to download the hcheck.sql script to perform this action.
Related articles.
DBMS_HCHECK.FULL
The FULL procedure performs all checks and displays the output on the screen, and writes it to a trace file. We can run it against the root container or a pluggable database.
--conn sys/SysPassword1@//localhost:1521/free as sysdba
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
SQL> set serveroutput on size unlimited
SQL> exec dbms_hcheck.full
dbms_hcheck on 26-JUL-2023 19:16:19
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11866_HCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- Mview ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- ValidDir ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 07/26 19:16:19 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- TableSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- PartCol ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ValidSeg ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- FetUet ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ValidInd ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ValidTab ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- CheckDual ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- NextObject ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- IdnseqObj ... 2300000000 > 1201000000 07/26 19:16:20 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 07/26 19:16:20 PASS
.- ObjError ... 2300000000 > 1102000000 07/26 19:16:20 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 07/26 19:16:20 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 07/26 19:16:20 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 07/26 19:16:20 PASS
---------------------------------------
26-JUL-2023 19:16:20 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11866_HCHECK.trc
PL/SQL procedure successfully completed.
SQL>
DBMS_HCHECK.CRITICAL
The CRITICAL procedure performs only critical checks and displays the output on the screen, and writes it to a trace file. We can run it against the root container or a pluggable database.
--conn sys/SysPassword1@//localhost:1521/free as sysdba
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
SQL> set serveroutput on size unlimited
SQL> execute dbms_hcheck.critical
dbms_hcheck on 26-JUL-2023 19:19:29
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11866_HCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg ... 2300000000 <= *All Rel* 07/26 19:19:29 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 07/26 19:19:29 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 07/26 19:19:29 PASS
---------------------------------------
26-JUL-2023 19:19:29 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11866_HCHECK.trc
PL/SQL procedure successfully completed.
SQL>
For more information see:
Hope this helps. Regards Tim...