How to Detect and Repair Corruption in Your Oracle Database

The DBMS_DICTIONARY_CHECK is a read-only and lightweight PL/SQL package procedure that helps you identify Oracle Database dictionary inconsistencies that are manifested in unexpected entries in the Oracle Database dictionary tables or invalid references between dictionary tables. Oracle Database dictionary inconsistencies can cause process failures and, in some cases, instance crash.

How to Execute a Dictionary Check

• First, The database must be on Oracle Database 19.22.0 or higher

Execute the Check

set serverout on
exec dbms_dictionary_check.full;

The database spools to the console and a trace file:

dbms_dictionary_check on 10-MAR-2024 13:31:56
----------------------------------------------
Catalog Version 19.0.0.0.0 (1900000000)
db_name: UP19
Is CDB?: NO
Trace File:
/u01/app/oracle/diag/rdbms/up19/UP19/trace/UP19_ora_3343_DICTCHECK.trc

                                Catalog       Fixed
Procedure Name                  Version    Vs Release    Timestamp      Result
--------------------------- ... ---------- -- ---------- -------------- ------
.- OIDOnObjCol		       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
.- LobNotInObj		       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
.- SourceNotInObj	       ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS

...

.- LobSeg                   ... 1900000000 <=  *All Rel* 03/02 13:31:56 PASS
---------------------------------------
02-MAR-2024 13:31:56  Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File:
/u01/app/oracle/diag/rdbms/up19/UP19/trace/UP19_ora_3343_DICTCHECK.trc

PL/SQL procedure successfully completed.

If there are any warnings or errors, you can find a detailed description of the check in the documentation. Some checks even have a fix mode that allows you to correct the corruption.

In multitenant, you run the check in the root container and all PDBs. The PDBs are most important.

Words of Advice. Run the check in due time before a major maintenance operation. Although many checks have a fix option, you sometimes need to engage with Oracle Support. This can take time, so don’t do it the day before an important upgrade.

AutoUpgrade makes your life easier. Of course, it can run the dictionary check for you. Add the following to your config file:

<prefix>.run_dictionary_health=full

During the pre-upgrade analysis, AutoUpgrade executes the dictionary check and stores the full report in the precheck folder in the AutoUpgrade log directory.