Oracle 23ai DBMS_HCHECK is your new best friend for Data Dictionary health
Posted by FatDBA on September 22, 2024
Today, while experimenting with Oracle 23ai new features, I came across a particularly helpful feature called DBMS_DICTIONARY_CHECK (referred to as DBMS_HCHECK in Oracle 23c). This tool proves to be both critical and incredibly useful for identifying and resolving data dictionary issues, which can sometimes be indirectly linked to errors like ORA-00600 or any inconsistencies or changes in DD.
I tested both options—FULL and CRITICAL—and found them highly effective. Both procedures are quick and performs a deep validation of the data dictionary, and while they print the results on the screen, they also write the output to a file on the operating system for further review. The FULL procedure conducts an exhaustive check, while the CRITICAL option focuses on only the most essential checks, making it quicker to execute. One of the most valuable aspects of these procedures is their ability not just to identify issues but also to repair them if you set the repair=>TRUE parameter.
The results of the execution are reported as:
CRITICAL: Requires immediate attention and fixing.
FAIL: High-priority issue that needs resolution.
WARN: Non-urgent but worth resolving.
PASS: No issues detected.
It brings back memories of the days when you had to manually download Oracle’s hcheck.sql script to analyze your data dictionary for known problems. Now, with this feature integrated directly into the database, it makes the process significantly more convenient and efficient.
Note: In Oracle 23c, this package is called DBMS_HCHECK, while in earlier versions (like Oracle 23.3ai), it’s referred to as DBMS_DICTIONARY_CHECK.
This integrated approach makes monitoring and maintaining the database’s health much simpler and more automated.
Below is how you perform a full check on the data dictionary.
SQL> set serveroutput on size unlimited
SQL> execute dbms_dictionary_check.full
dbms_dictionary_check on 22-SEP-2024 05:04:51
----------------------------------------------
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_3941_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- Mview ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- ValidDir ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 09/22 05:04:51 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ValidateSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- TableSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- PartCol ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- FetUet ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ValidInd ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ValidTab ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- CheckDual ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 09/22 05:04:52 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- NextObject ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- IdnseqObj ... 2300000000 > 1201000000 09/22 05:04:53 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 09/22 05:04:53 PASS
.- ObjError ... 2300000000 > 1102000000 09/22 05:04:53 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 09/22 05:04:53 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/22 05:04:53 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- LobSeg ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- ObjLogicalConstraints ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- SysSequences ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
.- ValidateFile ... 2300000000 <= *All Rel* 09/22 05:04:53 PASS
---------------------------------------
22-SEP-2024 05:04:53 Elapsed: 2 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:05:17
----------------------------------------------
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_3941_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg ... 2300000000 <= *All Rel* 09/22 05:05:17 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/22 05:05:17 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/22 05:05:17 PASS
.- SysSequences ... 2300000000 <= *All Rel* 09/22 05:05:17 PASS
---------------------------------------
22-SEP-2024 05:05:17 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:06:45
----------------------------------------------
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_3941_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- Mview ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- ValidDir ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 09/22 05:06:45 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ValidateSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- TableSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- PartCol ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- FetUet ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ValidInd ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ValidTab ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- CheckDual ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 09/22 05:06:46 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- NextObject ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- IdnseqObj ... 2300000000 > 1201000000 09/22 05:06:47 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 09/22 05:06:47 PASS
.- ObjError ... 2300000000 > 1102000000 09/22 05:06:47 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 09/22 05:06:47 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/22 05:06:47 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- LobSeg ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- ObjLogicalConstraints ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- SysSequences ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
.- ValidateFile ... 2300000000 <= *All Rel* 09/22 05:06:47 PASS
---------------------------------------
22-SEP-2024 05:06:47 Elapsed: 2 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:09: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_3941_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- Mview ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- ValidDir ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 09/22 05:09:29 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ValidateSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- TableSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- PartCol ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- FetUet ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ValidInd ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ValidTab ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- CheckDual ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- NextObject ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- IdnseqObj ... 2300000000 > 1201000000 09/22 05:09:30 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 09/22 05:09:30 PASS
.- ObjError ... 2300000000 > 1102000000 09/22 05:09:30 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 09/22 05:09:30 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/22 05:09:30 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- LobSeg ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- ObjLogicalConstraints ... 2300000000 <= *All Rel* 09/22 05:09:30 PASS
.- SysSequences ... 2300000000 <= *All Rel* 09/22 05:09:31 PASS
.- ValidateFile ... 2300000000 <= *All Rel* 09/22 05:09:31 PASS
---------------------------------------
22-SEP-2024 05:09:31 Elapsed: 2 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
dbms_dictionary_check on 22-SEP-2024 05:11:55
----------------------------------------------
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_3941_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- Mview ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- ValidDir ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 09/22 05:11:55 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ValidateSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- TableSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- PartCol ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- FetUet ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ValidInd ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ValidTab ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- CheckDual ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- NextObject ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- IdnseqObj ... 2300000000 > 1201000000 09/22 05:11:56 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 09/22 05:11:56 PASS
.- ObjError ... 2300000000 > 1102000000 09/22 05:11:56 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 09/22 05:11:56 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/22 05:11:56 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- LobSeg ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ObjLogicalConstraints ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- SysSequences ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
.- ValidateFile ... 2300000000 <= *All Rel* 09/22 05:11:56 PASS
---------------------------------------
22-SEP-2024 05:11:56 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
PL/SQL procedure successfully completed.
SQL>
This is how you can run a quick CRITICAL check on the data dictionary.
SQL> set serveroutput on size unlimited
SQL> execute dbms_dictionary_check.critical
dbms_dictionary_check on 22-SEP-2024 05:15:42
----------------------------------------------
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_3941_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg ... 2300000000 <= *All Rel* 09/22 05:15:42 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/22 05:15:42 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/22 05:15:42 PASS
.- SysSequences ... 2300000000 <= *All Rel* 09/22 05:15:42 PASS
---------------------------------------
22-SEP-2024 05:15:42 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
PL/SQL procedure successfully completed.
It also creates a report within your DIAG directory as well.
-rw-r-----. 1 oracle oinstall 15K Sep 22 05:15 FREE_ora_3941_DICTCHECK.trm
-rw-r-----. 1 oracle oinstall 35K Sep 22 05:15 FREE_ora_3941_DICTCHECK.trcAnd below is how you can even repair issues or inconsistencies with the data dictionary. In my case there isn’t any, but this is how you can do it.
SQL>
SQL>
SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE)
dbms_dictionary_check on 22-SEP-2024 05:13:36
----------------------------------------------
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_3941_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- Mview ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidDir ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidateSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- TableSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- PartCol ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- FetUet ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidInd ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidTab ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- CheckDual ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- NextObject ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- IdnseqObj ... 2300000000 > 1201000000 09/22 05:13:36 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 09/22 05:13:36 PASS
.- ObjError ... 2300000000 > 1102000000 09/22 05:13:36 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 09/22 05:13:36 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/22 05:13:36 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- LobSeg ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ObjLogicalConstraints ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- SysSequences ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
.- ValidateFile ... 2300000000 <= *All Rel* 09/22 05:13:36 PASS
---------------------------------------
22-SEP-2024 05:13:36 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_3941_DICTCHECK.trc
PL/SQL procedure successfully completed.
SQL>
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
Leave a comment