在Oracle 23ai中，新增了一个DBMS_DICTIONARY_CHECK包，允许我们检查数据库中的已知数据字典问题。
几年前，Oracle发布了hcheck.sql脚本（Doc ID 136697.1），以检查数据库中的已知数据字典问题。DBMS_DICTIONARY_CHECK软件包意味着我们不再需要下载hcheck.sql脚本来执行此操作。
针对hcheck.sql脚本的简单介绍，笔者简单描述下他的用处：
提供单个匿名 PL/SQL 块来检查常见的数据字典问题。 该脚本检查所选字典关系的一致性并查找某些已知问题 - 一些报告的“问题”将是正常的和预期的。 该脚本可用于 Oracle Release 9i 及以上版本。 hcheck8i.sql 包括 8.1。 它是一个轻量级的“只读”脚本，没有任何影响。 该脚本主要在 Oracle 支持的指导下使用。对于 Oracle 10.2.0.5 及以上版本，使用我提供的hcheck.sql即可。
注意：虽然此脚本已由 Oracle 支持和开发部门进行了内部测试，但在生产环境中运行之前，应先在开发或测试环境中运行（并解决任何问题）。
在Oracle 23ai中，提供了两个DBMS包：
DBMS_DICTIONARY_CHECK.FULL DBMS_DICTIONARY_CHECK.CRITICALDBMS_DICTIONARY_CHECK.FULL
FULL过程执行所有检查并在屏幕上显示输出，并将其写入跟踪文件。我们可以针对根CDB或PDB数据库运行它。
SQL> conn sys/oracle@abc as sysdba Connected. SQL> set serveroutput on size unlimited SQL> execute dbms_dictionary_check.full dbms_dictionary_check on 15-MAY-2024 10:47:13 ---------------------------------------------- Catalog Version 23.0.0.0.0 (2300000000) db_name: FREE Is CDB?: YES CON_ID: 3 Container: FREEPDB1 Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_580_DICTCHECK.trc Catalog Fixed Procedure Name Version Vs Release Timestamp Result ------------------------------ ... ---------- -- ---------- -------------- ------ .- OIDOnObjCol ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- LobNotInObj ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- SourceNotInObj ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- OversizedFiles ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- PoorDefaultStorage ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- PoorStorage ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- TabPartCountMismatch ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- TabComPartObj ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- Mview ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- ValidDir ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- DuplicateDataobj ... 2300000000 <= *All Rel* 05/15 10:47:13 PASS .- ObjSyn ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ObjSeq ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ValidateSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- UndoSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- IndexSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- IndexPartitionSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- TableSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- TablePartitionSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- TableSubPartitionSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- PartCol ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- IndPartObj ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- DuplicateBlockUse ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- FetUet ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- Uet0Check ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- SeglessUET ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ValidInd ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ValidTab ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- IcolDepCnt ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ObjIndDobj ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- TrgAfterUpgrade ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ObjType0 ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ValidOwner ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- StmtAuditOnCommit ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- PublicObjects ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- SegFreelist ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ValidDepends ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- CheckDual ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ObjectNames ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ChkIotTs ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- NoSegmentIndex ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- NextObject ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- DroppedROTS ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- FilBlkZero ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- DbmsSchemaCopy ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- IdnseqObj ... 2300000000 > 1201000000 05/15 10:47:14 PASS .- IdnseqSeq ... 2300000000 > 1201000000 05/15 10:47:14 PASS .- ObjError ... 2300000000 > 1102000000 05/15 10:47:14 PASS .- ObjNotLob ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- MaxControlfSeq ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- SegNotInDeferredStg ... 2300000000 > 1102000000 05/15 10:47:14 PASS .- SystemNotRfile1 ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ValidateTrigger ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ObjNotTrigger ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- InvalidTSMaxSCN ... 2300000000 > 1202000000 05/15 10:47:14 PASS .- OBJRecycleBin ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- LobSeg ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ObjLogicalConstraints ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- SysSequences ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS .- ValidateFile ... 2300000000 <= *All Rel* 05/15 10:47:14 PASS --------------------------------------- 15-MAY-2024 10:47:14 Elapsed: 1 secs --------------------------------------- Found 0 potential problem(s) and 0 warning(s) Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_580_DICTCHECK.trc PL/SQL procedure successfully completed. SQL>
在运行全面检查时，您可以选择使用
repair选项来解决不一致问题。有效值：
TRUE|
FALSE。默认：
FALSE。
SQL> set serveroutput on size unlimited SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE) dbms_dictionary_check on 15-MAY-2024 10:51:35 ---------------------------------------------- Catalog Version 23.0.0.0.0 (2300000000) db_name: FREE Is CDB?: YES CON_ID: 3 Container: FREEPDB1 Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_580_DICTCHECK.trc Catalog Fixed Procedure Name Version Vs Release Timestamp Result ------------------------------ ... ---------- -- ---------- -------------- ------ .- OIDOnObjCol ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- LobNotInObj ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- SourceNotInObj ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- OversizedFiles ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- PoorDefaultStorage ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- PoorStorage ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- TabPartCountMismatch ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- TabComPartObj ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- Mview ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ValidDir ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- DuplicateDataobj ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ObjSyn ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ObjSeq ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ValidateSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- UndoSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- IndexSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- IndexPartitionSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- TableSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- TablePartitionSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- TableSubPartitionSeg ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- PartCol ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- IndPartObj ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- DuplicateBlockUse ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- FetUet ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- Uet0Check ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- SeglessUET ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ValidInd ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ValidTab ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- IcolDepCnt ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ObjIndDobj ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- TrgAfterUpgrade ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ObjType0 ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ValidOwner ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- StmtAuditOnCommit ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- PublicObjects ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- SegFreelist ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- ValidDepends ... 2300000000 <= *All Rel* 05/15 10:51:35 PASS .- CheckDual ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- ObjectNames ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- ChkIotTs ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- NoSegmentIndex ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- NextObject ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- DroppedROTS ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- FilBlkZero ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- DbmsSchemaCopy ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- IdnseqObj ... 2300000000 > 1201000000 05/15 10:51:36 PASS .- IdnseqSeq ... 2300000000 > 1201000000 05/15 10:51:36 PASS .- ObjError ... 2300000000 > 1102000000 05/15 10:51:36 PASS .- ObjNotLob ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- MaxControlfSeq ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- SegNotInDeferredStg ... 2300000000 > 1102000000 05/15 10:51:36 PASS .- SystemNotRfile1 ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- ValidateTrigger ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- ObjNotTrigger ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- InvalidTSMaxSCN ... 2300000000 > 1202000000 05/15 10:51:36 PASS .- OBJRecycleBin ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- LobSeg ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- ObjLogicalConstraints ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- SysSequences ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS .- ValidateFile ... 2300000000 <= *All Rel* 05/15 10:51:36 PASS --------------------------------------- 15-MAY-2024 10:51:36 Elapsed: 1 secs --------------------------------------- Found 0 potential problem(s) and 0 warning(s) Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_580_DICTCHECK.trc PL/SQL procedure successfully completed. SQL>
DBMS_DICTIONARY_CHECK.CRITICAL
CRITICAL过程只执行关键检查，并在屏幕上显示输出，并将其写入跟踪文件。可以针对根容器或可插拔数据库运行它。
SQL> set serveroutput on size unlimited SQL> execute dbms_dictionary_check.critical dbms_dictionary_check on 15-MAY-2024 10:52:18 ---------------------------------------------- Catalog Version 23.0.0.0.0 (2300000000) db_name: FREE Is CDB?: YES CON_ID: 3 Container: FREEPDB1 Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_580_DICTCHECK.trc Catalog Fixed Procedure Name Version Vs Release Timestamp Result ------------------------------ ... ---------- -- ---------- -------------- ------ .- UndoSeg ... 2300000000 <= *All Rel* 05/15 10:52:18 PASS .- MaxControlfSeq ... 2300000000 <= *All Rel* 05/15 10:52:18 PASS .- InvalidTSMaxSCN ... 2300000000 > 1202000000 05/15 10:52:18 PASS .- SysSequences ... 2300000000 <= *All Rel* 05/15 10:52:18 PASS --------------------------------------- 15-MAY-2024 10:52:18 Elapsed: 0 secs --------------------------------------- Found 0 potential problem(s) and 0 warning(s) Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_580_DICTCHECK.trc PL/SQL procedure successfully completed. SQL>
CRITICAL: 需要立即修复。 FAIL: 需要优先解决。 WARN: 很好解决。 PASS: 没有问题。
参考文档：
https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms-dictionary-check.html#GUID-BD44F6AB-437B-4351-9CAE-9190675FAD21
