Inconsistencies in the Data Dictionary are not common and rarely occur, but when they do, they can be quite challenging. One such example you may have encountered is an invalid reference, such as a segment whose details are missing in DBA_OBJECTS.
Until version 23ai, we could identify these types of inconsistencies using the hcheck.sql script. However, in Oracle version 23ai (specifically 23.3), a package called DBMS_DICTIONARY_CHECK was introduced, which helps verify the consistency of the Data Dictionary. This package includes various procedures:
SQL> desc DBMS_DICTIONARY_CHECK
PROCEDURE CRITICAL
PROCEDURE FULL
PROCEDURE DUPLICATEDATAOBJ
PROCEDURE IDNSEQOBJ
PROCEDURE IDNSEQSEQ
PROCEDURE INDEXPARTITIONSEG
PROCEDURE INDEXSEG
PROCEDURE INDINDPARMISMATCH
PROCEDURE INDPARTOBJ
PROCEDURE LOBSEG
PROCEDURE MVIEW
PROCEDURE NEXTOBJECT
PROCEDURE NOSEGMENTINDEX
PROCEDURE OBJECTNAMES
PROCEDURE OBJERROR
….
Among these, the FULL procedure provides comprehensive information about the validity of the Data Dictionary. Below is a sample output of its execution:
SQL> set serveroutput on size unlimited
SQL> exec DBMS_DICTIONARY_CHECK.FULL;
dbms_dictionary_check on 06-DEC-2023 18:25:16
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- Mview ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- ValidDir ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TableSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- PartCol ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- FetUet ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidInd ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidTab ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- CheckDual ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- NextObject ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IdnseqObj ... 2300000000 > 1201000000 12/06 18:25:17 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 12/06 18:25:17 PASS
.- ObjError ... 2300000000 > 1102000000 12/06 18:25:17 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 12/06 18:25:17 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 12/06 18:25:17 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- LobSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
---------------------------------------
06-DEC-2023 18:25:17 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
PL/SQL procedure successfully completed.
Now we’ll intentionally create an inconsistency in the Data Dictionary and rerun the package. For this, we’ll delete a record from the SEQ$ table (Note: modifying Data Dictionary tables can lead to database corruption!):
SQL> select obj# from seq$ where rownum=1;
OBJ#
----------
130
SQL> delete seq$ where OBJ#=130;
1 row deleted.
SQL> commit;
Commit complete.
We now rerun DBMS_DICTIONARY_CHECK:
SQL> exec DBMS_DICTIONARY_CHECK.full;
dbms_dictionary_check on 06-DEC-2023 18:31:40
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- Mview ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- ValidDir ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 12/06 18:31:41 WARN
HCKW-0006: SEQ$ entry missing for OBJ$ type#=6 (Doc ID 1360524.1)
OBJ#=130 Name=SYS.UGROUP_SEQUENCE
.- UndoSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TableSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- PartCol ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- FetUet ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidInd ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidTab ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- CheckDual ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- NextObject ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IdnseqObj ... 2300000000 > 1201000000 12/06 18:31:41 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 12/06 18:31:41 PASS
.- ObjError ... 2300000000 > 1102000000 12/06 18:31:41 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 12/06 18:31:41 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 12/06 18:31:41 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- LobSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
---------------------------------------
06-DEC-2023 18:31:41 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
PL/SQL procedure successfully completed.
As shown above, the error related to object number 130 is clearly reported.
HCKW-0006: SEQ$ entry missing for OBJ$ type#=6 (Doc ID 1360524.1)
OBJ#=130 Name=SYS.UGROUP_SEQUENCE
Another important procedure in this package is CRITICAL:
SQL> set serveroutput on size unlimited
SQL> exec DBMS_DICTIONARY_CHECK.CRITICAL;
dbms_dictionary_check on 06-DEC-2023 18:36:01
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg ... 2300000000 <= *All Rel* 12/06 18:36:01 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/06 18:36:01 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 12/06 18:36:01 PASS
---------------------------------------
06-DEC-2023 18:36:01 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
PL/SQL procedure successfully completed.
Top comments (0)