DBMS_DICTIONARY_CHECK package has been back-ported from 23c to oracle 19c when applying January 2024 RU [19.22]. The package is a read-only PL/SQL package procedure that helps you identify Oracle Database dictionary. its in some sense the evolution of hcheck.sql script that you need to download and install, this is not needed anymore … this package is now available as built-in is really a positive move.
To Run the Package to examine your database state:
SQL> set serveroutput on size unlimited
SQL> execute dbms_dictionary_check.full;
A detailed report will be generated as shown below:
To Check “Critical” Findings and the status:
SQL> execute dbms_dictionary_check.critical
The following are the 3 main areas that package will consider and flag “Critical”and should be in “PASS” or valid state otherwise it has negative impact and will require your attention/fix:
UndoSeg: Checks that undo segment has a valid entry in SEG$ (My Oracle Support Note 1360527.1)
MaxControlfSeq: Checks if Control Seq is near the limit (My Oracle Support Note 2128446.1)
InvalidTSMaxSCN: Checks for invalid SCN entries in tablespaces (My Oracle Support Note 1360208.1)
To Run a FULL Repair for the reported findings:
SQL> set serveroutput on size unlimited
SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE);
For further reference documentation: https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms-dictionary-check.html#GUID-BD44F6AB-437B-4351-9CAE-9190675FAD21