General Information
Library Note
Morgan's Library Page Header
Purpose
This package is provides an API to assist in the identification of data dictionary metadata inconsistencies.
AUTHID
DEFINER
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_DICTIONARY_CHECK' UNIONReturns 56 objects
Documented
Yes
First Available
23ai
Pragmas
PRAGMA SUPPEMENTAL_LOG_DATA(default, READ_ONLY);
Security Model
Owned by SYS with EXECUTE granted to the DBA role.
Source
{ORACLE_HOME}/rdbms/admin/dbmsdictcheck.sql
Subprograms
CBOHILO (new 23ai)
Invalid histogram metadata check
dbms_dictionary_check.cboHiLo;
set serveroutput ondbms_dictionary_check.cboHiLo ;PL/SQL producedure successfully completed.
CHECKDUAL (new 23ai)
Invalid entries in dual check
dbms_dictionary_check.checkDual;
set serveroutput ondbms_dictionary_check.checkDual ;.- CheckDual ... 2300000000 <= *All Rel* 05/10 20:09:23 PASS
CHKIOTTS (new 23ai)
Invalid entries in dual check
dbms_dictionary_check.chkIotTs;
set serveroutput ondbms_dictionary_check.chkIotTs ;.- ChkIotTs ... 2300000000 <= *All Rel* 05/10 20:10:08 PASS
CRITICAL (new 23ai)
Execute "critical" checks only
dbms_dictionary_check.critical;
set serveroutput ondbms_dictionary_check.critical ;dbms_dictionary_check on 10-MAY-2023 20:05:03
DBMSSCHEMACOPY (new 23ai)
Checks for invalid execution of DBMS_SCHEMA_COPY
dbms_dictionary_check.dbmsSchemaCopy;
set serveroutput ondbms_dictionary_check.dmsSchemaCopy ;.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 05/10 20:10:59 PASS
DICTOWNNONDEFAULTSYSTEM (new 23ai)
Checks that the users SYS and SYSTEM have default tablespace SYSTEM
dbms_dictionary_check.dictOwnNonDefaultSystem;
set serveroutput ondbms_dictionary_check.dictOwnNonDefaultSystem ;.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 05/10 20:11:15 PASS
DROPPEDROTS (new 23ai)
Checks for valid entries in a read-only tablespace
dbms_dictionary_check.droppedROTS;
set serveroutput ondbms_dictionary_check.droppedROTS ;.- DroppedROTS ... 2300000000 <= *All Rel* 05/10 20:12:51 PASS
DUPLICATEBLOCKUSE (new 23ai)
Checks for a segment header block is used by only one segment
dbms_dictionary_check.duplicateBlockUse;
set serveroutput ondbms_dictionary_check.duplicateBlockUse ;.- DuplicateBlockUse ... 2300000000 <= *All Rel* 05/10 20:12:51 PASS
DUPLICATEDATAOBJ (new 23ai)
Checks for duplicate segment data_object_id
dbms_dictionary_check.duplicateDataObj;
set serveroutput ondbms_dictionary_check.duplicateDataObj ;.- DuplicateDataobj ... 2300000000 <= *All Rel* 05/10 20:13:08 PASS
EXTENTLESSSEG (new 23ai)
Checks SEG$/UET$ mismatch in a dictionary-managed tablespace
dbms_dictionary_check.extentlessSeg;
set serveroutput ondbms_dictionary_check.extentlessSeg ;PL/SQL procedure successfully completed.
FETUET (new 23ai)
Checks valid free/used space in a dictionary-managed tablespace
dbms_dictionary_check.FetUet;
set serveroutput ondbms_dictionary_check.fetUet ;.- FetUet ... 2300000000 <= *All Rel* 05/10 20:13:53 PASS
FILBLKZERO (new 23ai)
Checks for zero data block address
dbms_dictionary_check.filBlkZero;
set serveroutput ondbms_dictionary_check.filBlkZero ;
FULL (new 23ai)
Execute all checks
dbms_dictionary_check.full;
set serveroutput ondbms_dictionary_check.full ;dbms_dictionary_check on 10-MAY-2023 20:04:19 .- ValidDir ... 2300000000 <= *All Rel* 05/10 20:04:19 PASS
ICOLDEPCNT (new 23ai)
Checks valid entries in ICOLDEP$
dbms_dictionary_check.iColDepCnt;
set serveroutput ondbms_dictionary_check.iColDepCnt ;.- IcolDepCnt ... 2300000000 <= *All Rel* 05/10 20:14:26 PASS
IDNSEQOBJ (new 23ai)
Checks that Identity column has a valid object
dbms_dictionary_check.idnseqObj;
set serveroutput ondbms_dictionary_check.idnseqObj ;.- IdnseqObj ... 2300000000 > 1201000000 05/10 20:14:44 PASS
IDNSEQSEQ (new 23ai)
Checks that a sequence has a valid object
dbms_dictionary_check.idnseqSeq;
set serveroutput ondbms_dictionary_check.idnseqSeq ;.- IdnseqSeq ... 2300000000 > 1201000000 05/10 20:15:07 PASS
INDEXPARTITIONSEG (new 23ai)
Checks that an index partition has a valid entry in SEG$
dbms_dictionary_check.indexPartitionSeg;
set serveroutput ondbms_dictionary_check.indexPartitionSeg ;.- IndexPartitionSeg ... 2300000000 <= *All Rel* 05/10 20:15:21 PASS
INDEXSEG (new 23ai)
Checks that an index segment has a valid entry in SEG$
dbms_dictionary_check.indexSeg;
set serveroutput ondbms_dictionary_check.indexSeg ;.- IndexSeg ... 2300000000 <= *All Rel* 05/10 20:15:31 PASS
INDEXSUBPARTITIONSEG (new 23ai)
Checks that an index sub-partition has a valid entry in SEG$
dbms_dictionary_check.indexSubPartitionSeg;
set serveroutput ondbms_dictionary_check.indexSubPartitionSeg ;.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 05/10 20:16:43 PASS
INDINDPARMISMATCH (new 23ai)
Checks for index name mismatch between partitions
dbms_dictionary_check.indIndParMismatch;
set serveroutput ondbms_dictionary_check.indIndParMismatch ;PL/SQL procedure successfully completed.
INDPARTOBJ (new 23ai)
Checks that an index partition has an entry in OBJ$
dbms_dictionary_check.indPartObj;
set serveroutput ondbms_dictionary_check.indPartObj ;.- IndPartObj ... 2300000000 <= *All Rel* 05/10 20:16:30 PASS
INVALIDTSMAXSCN (new 23ai)
Check for invalid SCN entries in tablespaces
dbms_dictionary_check.invalidTSMaxSCN;
set serveroutput ondbms_dictionary_check.invalidTSMaxSCN ;.- InvalidTSMaxSCN ... 2300000000 > 1202000000 05/10 20:17:50 PASS
INVCORRAUDIT (new 23ai)
Check for invalid AUDIT$ entries
dbms_dictionary_check.invCorrAudit;
set serveroutput ondbms_dictionary_check.invCorrAudit ;PL/SQL procedure successfully completed.
LOBNOTINOBJ (new 23ai)
Check for LOB segments is not in OBJ$
dbms_dictionary_check.lobNotInObj;
set serveroutput ondbms_dictionary_check.lobNotInObj ;.- LobNotInObj ... 2300000000 <= *All Rel* 05/10 20:12:51 PASS
LOBSEG (new 23ai)
Check that a lob segment has a valid entry in SEG$
dbms_dictionary_check.lobseg;
exec dbms_dictionary_check.lobseg ;dbms_dictionary_check on 01-SEP-2025 09:07:25
MAXCONTROLFSEQ (new 23ai)
Checks if Control Seq is near the limit
dbms_dictionary_check.maxControlFSeq;
set serveroutput ondbms_dictionary_check.maxControlFSeq ;.- MaxControlfSeq ... 2300000000 <= *All Rel* 05/10 20:12:51 PASS
MVIEW (new 23ai)
Checks for invalid materialized view entries
dbms_dictionary_check.mview;
set serveroutput ondbms_dictionary_check.mview ;.- Mview ... 2300000000 <= *All Rel* 05/10 20:12:51 PASS
NEXTOBJECT (new 23ai)
Checks for valid data_object_ids
dbms_dictionary_check.nextObject;
set serveroutput ondbms_dictionary_check.nextObject ;.- NextObject ... 2300000000 <= *All Rel* 05/10 20:12:51 PASS
NOSEGMENTINDEX (new 23ai)
Check for NOSEGMENT indexes
dbms_dictionary_check.noSegmentIndex;
set serveroutput ondbms_dictionary_check.noSegmentIndex ;.- NoSegmentIndex ... 2300000000 <= *All Rel* 05/11 13:22:10 PASS
OBJECTNAMES (new 23ai)
Checks if an object has the same name as its schema owner
dbms_dictionary_check.objectNames;
set serveroutput ondbms_dictionary_check.objectNames ;.- ObjectNames ... 2300000000 <= *All Rel* 05/11 13:22:36 PASS
OBJERROR (new 23ai)
Checks that an object error is valid
dbms_dictionary_check.objError;
set serveroutput ondbms_dictionary_check.objError ;.- ObjError ... 2300000000 > 1102000000 05/11 13:22:46 PASS
OBJINDDOBJ (new 23ai)
Checks for index data_object_id mismatch between OBJ$ and IND$
dbms_dictionary_check.objIndDObj;
set serveroutput ondbms_dictionary_check.objIndDObj ;.- ObjIndDobj ... 2300000000 <= *All Rel* 05/11 13:22:26 PASS
OBJLOGICALCONSTRAINTS (new 23ai)
Check logical constraints in OBJ$
dbms_dictionary_check.objLogicalConstraints;
exec dbms_dictionary_check.objLogicalConstraints ;.- ObjLogicalConstraints ... 2300000000 <= *All Rel* 09/01 09:08:00 PASS
objn
OBJNOTLOB (new 23ai)
Checks that a LOB object has an entry in LOB$
dbms_dictionary_check.objNotLOB;
set serveroutput ondbms_dictionary_check.objNotLOB ;.- ObjNotLob ... 2300000000 <= *All Rel* 05/11 13:22:57 PASS
OBJNOTTRIGGER (new 23ai)
Checks if an object trigger is not in TRIGGER$
dbms_dictionary_check.objNotTrigger;
set serveroutput ondbms_dictionary_check.objNotTrigger ;.- ObjNotTrigger ... 2300000000 <= *All Rel* 05/11 13:23:08 PASS
OBJRECYCLEBIN (new 23ai)
Checks that recycle bin objects in OBJ$ exist in RECYCLEBIN$
dbms_dictionary_check.objRecycleBin;
set serveroutput ondbms_dictionary_check.objRecycleBin ;.- OBJRecycleBin ... 2300000000 <= *All Rel* 05/10 20:07:01 PASS
OBJSEQ (new 23ai)
Checks that a sequence has a valid entry in OBJ$
dbms_dictionary_check.objSeq;
set serveroutput ondbms_dictionary_check.objSeq ;.- ObjSeq ... 2300000000 <= *All Rel* 05/11 13:23:25 PASS
OBJSYN (new 23ai)
Checks that a synonym has a valid entry in OBJ$
dbms_dictionary_check.objSyn;
set serveroutput ondbms_dictionary_check.objSyn ;.- ObjSyn ... 2300000000 <= *All Rel* 05/11 13:23:36 PASS
OBJTYPE0 (new 23ai)
Checks that OBJ$ has a valid type greater than 0
dbms_dictionary_check.objType0;
set serveroutput ondbms_dictionary_check.objType0 ;.- ObjType0 ... 2300000000 <= *All Rel* 05/11 13:23:49 PASS
OIDONOBJCOL (new 23ai)
Checks if an object type column is not in OID$
dbms_dictionary_check.oidOnObjCol;
set serveroutput ondbms_dictionary_check.oidOnObjCol ;.- OIDOnObjCol ... 2300000000 <= *All Rel* 05/11 13:24:07 PASS
OVERSIZEDFILES (new 23ai)
Checks for oversized database files
dbms_dictionary_check.oversizedFiles;
set serveroutput ondbms_dictionary_check.oversizedFiles ;.- OversizedFiles ... 2300000000 <= *All Rel* 05/11 13:24:21 PASS
PARTCOL (new 23ai)
Checks for valid entry of column partition
dbms_dictionary_check.partCol;
set serveroutput ondbms_dictionary_check.partCol ;.- PartCol ... 2300000000 <= *All Rel* 05/11 13:24:28 PASS
PARTSUBPARTMISMATCH (new 23ai)
Checks valid partition methods
dbms_dictionary_check.partSubpartMismatch;
set serveroutput ondbms_dictionary_check.partSubpartMismatch ;PL/SQL procedure successfully completed.
POORDEFAULTSTORAGE (new 23ai)
Checks tablespace default storage clauses
dbms_dictionary_check.poorDefaultStorage;
set serveroutput ondbms_dictionary_check.poorDefaultStorage ;.- PoorDefaultStorage ... 2300000000 <= *All Rel* 05/11 13:24:59 PASS
POORSTORAGE (new 23ai)
Checks objects storage clause
dbms_dictionary_check.poorStorage;
set serveroutput ondbms_dictionary_check.poorStorage ;.- PoorStorage ... 2300000000 <= *All Rel* 05/11 13:25:08 PASS
PUBLICOBJECTS (new 23ai)
Checks that objects are not owned by PUBLIC
dbms_dictionary_check.publicObjects;
set serveroutput ondbms_dictionary_check.publicObjects ;.- PublicObjects ... 2300000000 <= *All Rel* 05/11 13:25:20 PASS
SEGFREELIST (new 23ai)
Checks that a LOB segment has a valid free list group
dbms_dictionary_check.segFreelist;
set serveroutput ondbms_dictionary_check.segFreelist ;.- SegFreelist ... 2300000000 <= *All Rel* 05/11 13:25:30 PASS
SEGLESSUET (new 23ai)
Checks UET$/SEG$ mismatch in a dictionary-managed tablespace
dbms_dictionary_check.segLessUET;
set serveroutput ondbms_dictionary_check.segLessUET ;.- SeglessUET ... 2300000000 <= *All Rel* 05/11 13:29:17 PASS
SEGNOTINDEFERREDSTG (new 23ai)
Checks for an invalid deferred segment
dbms_dictionary_check.segNotInDeferredStg;
set serveroutput ondbms_dictionary_check.segNotInDeferredStg ;.- SegNotInDeferredStg ... 2300000000 > 1102000000 05/11 13:29:35 PASS
SOURCENOTINOBJ (new 23ai)
Checks if an entry in SOURCE$ is not in OBJ$
dbms_dictionary_check.sourceNotInObj;
set serveroutput ondbms_dictionary_check.sourceNotInObj ;.- SourceNotInObj ... 2300000000 <= *All Rel* 05/11 13:29:49 PASS
STMTAUDITONCOMMIT (new 23ai)
Checks valid entries for STMT_AUDIT_OPTION_MAP
dbms_dictionary_check.stmtAuditOnCommit;
set serveroutput ondbms_dictionary_check.stmtAuditOnCommit ;.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 05/11 13:37:03 PASS
SYSSEQUENCES (new 23ai)
Check
inconsistencies with critical sequences owned by SYS
dbms_dictionary_check.sysSequences(repair
IN BOOLEAN DEFAULT FALSE);
exec
dbms_dictionary_check.sysSequences (TRUE);.- SysSequences ... 2300000000 <= *All Rel* 09/01 09:08:56 PASS
SYSTEMNOTRFILE1 (new 23ai)
Checks that the system tablespace has a relative file number 1
dbms_dictionary_check.systemNotRFile1;
set serveroutput ondbms_dictionary_check.systemNotRFile1 ;.- SystemNotRfile1 ... 2300000000 <= *All Rel* 05/11 13:31:19 PASS
TABCOMPARTOBJ (new 23ai)
Checks that the composite partition has a valid entry in OBJ$
dbms_dictionary_check.tabComPartObj;
set serveroutput ondbms_dictionary_check.tabComPartObj ;.- TabComPartObj ... 2300000000 <= *All Rel* 05/11 13:31:33 PASS
TABLEPARTITIONSEG (new 23ai)
Checks that a table partition has a valid entry in SEG$
dbms_dictionary_check.tablePartitionSeg;
set serveroutput ondbms_dictionary_check.tablePartitionSeg ;.- TablePartitionSeg ... 2300000000 <= *All Rel* 05/11 13:31:49 PASS
TABLESEG (new 23ai) (new 23ai)
Checks that a table has a valid entry in SEG$
dbms_dictionary_check.tableSeg;
set serveroutput ondbms_dictionary_check.tableSeg ;.- TableSeg ... 2300000000 <= *All Rel* 05/11 13:32:34 PASS
TABLESUBPARTITIONSEG (new 23ai)
Checks that a table sub-partition has a valid entry in SEG$
dbms_dictionary_check.tableSubpartitionSeg;
set serveroutput ondbms_dictionary_check.tableSubpartitionSeg ;.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 05/11 13:32:47 PASS
TABPARTCOUNTMISMATCH (new 23ai)
Checks invalid data between OBJ$-PARTOBJ$ and TABPART$
dbms_dictionary_check.tabPartCountMismatch;
set serveroutput ondbms_dictionary_check.tabPartCountMismatch ;.- TabPartCountMismatch ... 2300000000 <= *All Rel* 05/11 13:32:57 PASS
TRGAFTERUPGRADE (new 23ai)
Checks valid entries in triggers
dbms_dictionary_check.trgAfterUpgrade;
set serveroutput ondbms_dictionary_check.trgAfterUpgrade ;.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 05/11 13:33:07 PASS
UET0CHECK (new 23ai)
Checks valid first extent in a dictionary-managed tablespace
dbms_dictionary_check.uet0Check;
set serveroutput ondbms_dictionary_check.uet0Check ;.- Uet0Check ... 2300000000 <= *All Rel* 05/11 13:33:19 PASS
UNDOSEG (new 23ai)
Checks that undo segment has a valid entry in SEG$
dbms_dictionary_check.undoSeg;
set serveroutput ondbms_dictionary_check.undoSeg ;.- UndoSeg ... 2300000000 <= *All Rel* 05/11 13:33:26 PASS
VALIDATEFEDOBJ (new 23ai)
Checks for obj$ entries marked with FedApps in Non-CDB databases
dbms_dictionary_check.validateFedObj(repair IN BOOLEAN DEFAULT FALSE);
exec dbms_dictionary_check.validateFedObj (TRUE);PL/SQL procedure successfully completed.
VALIDATEFILE (new 23ai)
Checks for invalid file$ entries
dbms_dictionary_check.validateFile(repair IN BOOLEAN DEFAULT FALSE);
exec dbms_dictionary_check.validateFile (TRUE);.- ValidateFile ... 2300000000 <= *All Rel* 09/01 09:10:38 PASS
VALIDATEOBJSTUB (new 23ai)
Check for orphaned stubs and removes identified orphaned stubs
dbms_dictionary_check.validateObjStub(repair IN BOOLEAN DEFAULT FALSE);
exec dbms_dictionary_check.validateObjStub (TRUE);.- ValidateObjStub ... 2300000000 <= *All Rel* 09/01 09:11:07 PASS
VALIDATESEG (new 23ai)
Checks that a segment in SEG$ has an entry in its parent
dbms_dictionary_check.validateSeg;
set serveroutput ondbms_dictionary_check.validateSeg ;.- ValidSeg ... 2300000000 <= *All Rel* 05/11 13:33:36 PASS
VALIDATETRIGGER (new 23ai)
Checks that triggers have valid entries in their parents
dbms_dictionary_check.validateTrigger;
set serveroutput ondbms_dictionary_check.validateTrigger ;.- ValidateTrigger ... 2300000000 <= *All Rel* 05/11 13:33:44 PASS
VALIDDEPENDS (new 23ai)
Checks for valid dependency timestamps
dbms_dictionary_check.validDepends;
set serveroutput ondbms_dictionary_check.validDepends ;.- ValidDepends ... 2300000000 <= *All Rel* 05/11 13:33:54 PASS
VALIDDIR (new 23ai)
Checks that the directory object has valid entries
dbms_dictionary_check.validDir;
set serveroutput ondbms_dictionary_check.validDir ;.- ValidDir ... 2300000000 <= *All Rel* 05/11 13:34:06 PASS
VALIDIND (new 23ai)
Checks that an index in OBJ$ has a corresponding entry in the index dictionary
dbms_dictionary_check.validInd;
set serveroutput ondbms_dictionary_check.validInd ;.- ValidInd ... 2300000000 <= *All Rel* 05/11 13:34:16 PASS
VALIDOWNER (new 23ai)
Checks that an entry in OBJ$ has a valid user ID
dbms_dictionary_check.validOwner;
set serveroutput ondbms_dictionary_check.validOwner ;.- ValidOwner ... 2300000000 <= *All Rel* 05/11 13:34:24 PASS
VALIDTAB (new 23ai)
Checks that a table in OBJ$ has a corresponding entry in TAB$
dbms_dictionary_check.validTab;
set serveroutput ondbms_dictionary_check.validTab ;.- ValidTab ... 2300000000 <= *All Rel* 05/11 13:34:30 PASS
WARNICOLDEP (new 23ai)
Checks that an index does not have an ADT (object column)
dbms_dictionary_check.warnIcolDep;
set serveroutput ondbms_dictionary_check.warnIcolDep ;PL/SQL procedure successfully completed.
WARNINGTSMAXSCN (new 23ai)
Checks exposed SCN entries in tablespaces
dbms_dictionary_check.warningTSMaxSCN;
set serveroutput ondbms_dictionary_check.warningTSMaxSCN ;PL/SQL procedure successfully completed.
