Database health Check PLSQL
Introduction
DBMS_DICTIONARY_CHECK helps you find Oracle Database dictionary issues. For example, invalid RI or segments with no object. A full list can be found in the documentation located here and are repeated below for convenience.
- A lob segment not in OBJ$
- An entry in SOURCE$ not in OBJ$
- Invalid data between OBJ$-PARTOBJ$ and TABPART$
- A segment with no owner
- A materialized segment with no entry in seg$
- A segment with no object entry
- A recycle bin object not in the recyclebin$
- Check if Control Seq is near the limit
There are two types of checks you can run. Critical or Full. There is also an option to repair when running a full check. See below.
Critical Check
- sqlplus sys/<password>@//localhost:1521/mydb1 as sysdba
- SQL> set serveroutput on size unlimited
- SQL> execute dbms_dictionary_check.critical
Full Check
- sqlplus sys/<password>@//localhost:1521/mydb1 as sysdba
- SQL> set serveroutput on size unlimited
- SQL> execute dbms_dictionary_check.full
Repair Option
- sqlplus sys/<password>@//localhost:1521/mydb1 as sysdba
- SQL> set serveroutput on size unlimited
- SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE)
Important Information
The report will generate a trace file. An example is listed below. But also output the following high level assesments
- CRITICAL - Requires an immediate fix.
- FAIL - Requires resolution on priority.
- WARN - Good to resolve.
- PASS - No issues.
NOTE - You should always raise an SR with Oracle support before applying any fixes or running the repair option.
Example Output
Critical
dbms_dictionary_check on 27-SEP-2023 11:23:02
----------------------------------------------
Catalog Version 21.0.0.0.0 (2100000000)
db_name: MYCDB1
Is CDB?: YES CON_ID: 4 Container: MYPDB1
Trace File: /oracle/log/diag/rdbms/mycdb1/mypdb1/trace/mypdb1_ora_232O6_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- UndoSeg ... 2300000000 <= *All Rel* 03/07 03:12:23 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 03/07 03:12:23 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 03/07 03:12:23 PASS
---------------------------------------
27-SEP-2023 11:23:02 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)Full
dbms_dictionary_check on 27-SEP-2023 15:28:21
----------------------------------------------
Catalog Version 21.0.0.0.0 (2300000000)
db_name: MYCDB1
Is CDB?: YES CON_ID: 4 Container: MYPDB1
Trace File: /oracle/log/diag/rdbms/mycdb1/mypdb1/trace/mypdb1_ora_26786_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 09/27 15:28:21 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 09/27 15:28:21 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 09/27 15:28:21 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 09/27 15:28:21 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 09/27 15:28:21 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 09/27 15:28:21 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- Mview ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- ValidDir ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- TableSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 FAIL
HCKE-0019: Orphaned TAB$ (no SEG$) (Doc ID 1360889.1)
ORPHAN TAB$: OBJ#=82231 DOBJ#=83241 TS=6 RFILE/BLOCK=10/12 TABLE=SYS.ORPHANSEG BOBJ#=
.- TablePartitionSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- PartCol ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- ValidSeg ... 2300000000 <= *All Rel* 09/27 15:28:23 FAIL
HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1)
ORPHAN SEG$: SegType=LOB TS=6 RFILE/BLOCK=7/21
.- IndPartObj ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- FetUet ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 09/27 15:28:23 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ValidInd ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ValidTab ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- CheckDual ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- NextObject ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- IdnseqObj ... 2300000000 > 1201000000 09/27 15:28:28 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 09/27 15:28:28 PASS
.- ObjError ... 2300000000 > 1102000000 09/27 15:28:28 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 09/27 15:28:28 FAIL
HCKE-0049: OBJ$ LOB entry has no LOB$ or LOBFRAG$ entry (Doc ID 2125104.1)
OBJ$ LOB has no LOB$ entry: Obj=88243 Owner: MYTAB LOB Name: MYLOB
.- MaxControlfSeq ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 09/27 15:28:28 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 09/27 15:28:28 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 09/27 15:28:28 PASS
---------------------------------------
27-SEP-2023 15:28:28 Elapsed: 7 secs
---------------------------------------
Found 3 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or notFull with Repair
dbms_dictionary_check on 04-OCT-2023 01:35:37
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: MYCDB1
Is CDB?: YES CON_ID: 4 Container: MYPDB1
Trace File: /oracle/log/diag/rdbms/mycdb1/mypdb1/trace/mypdb1_ora_25586_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 09/25 12:46:37 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 09/25 12:46:37 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 09/25 12:46:39 FAIL
HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1)
SOURCE$ has 22 rows for 1 OBJ# values not in OBJ$
INCONSISTENCY REPAIRED - Check the trace file for repair details:
SourceNotInObj_Repair: DELETED 10 objects from SOURCE$ not found in OBJ$
.- OversizedFiles ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- Mview ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- ValidDir ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 09/25 12:46:39 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 09/25 12:46:42 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 09/25 12:46:42 PASS
.- ValidateSeg ... 2300000000 <= *All Rel* 09/25 12:46:42 FAIL
.- TableSeg ... 2300000000 <= *All Rel* 09/25 12:46:42 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 09/25 12:46:42 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 09/25 12:46:42 PASS
ORPHAN SEG$: SegType=DATA TS=6 RFILE/BLOCK=9/5
^ Segment entry repaired - Converted to TEMPORARY
INCONSISTENCY REPAIRED - Check the trace file for repair details:
ValidateSeg repaired 1 Orphan Seg$ entries
.- UndoSeg ... 2300000000 <= *All Rel* 09/25 12:46:42 PASS
...
---------------------------------------
25-SEP-2023 12:46:42 Elapsed: 5 secs
---------------------------------------
Found 2 potential problem(s) and 0 warning(s)
Repaired 23 item(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or notPublished 4th Oct 2023
