作者：Digital Observer（施嘉伟）
Oracle ACE Associate: Database
PostgreSQL ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问
国内某高校企业实践指导教师
公众号：Digital Observer；CSDN：施嘉伟；ITPUB：sjw1933；PGFans：施嘉伟。
在Oracle 23ai中，新增了一个DBMS_DICTIONARY_CHECK包，允许我们检查数据库中的已知数据字典问题。几年前，Oracle发布了hcheck.sql脚本（Doc ID 136697.1），以检查数据库中的已知数据字典问题。DBMS_DICTIONARY_CHECK软件包意味着我们不再需要下载hcheck.sql脚本来执行此操作。
如果您想下载hcheck.sql脚本，可以扫描二维码下载！
针对hcheck.sql脚本的简单介绍，笔者简单描述下他的用处：
提供单个匿名 PL/SQL 块来检查常见的数据字典问题。
该脚本检查所选字典关系的一致性并查找某些已知问题。
它是一个轻量级的“只读”脚本，没有任何影响。
对于 Oracle 10.2.0.5 及以上版本，使用我提供的hcheck.sql即可。
注意：虽然此脚本已由 Oracle 支持和开发部门进行了内部测试，但在生产环境中运行之前，应先在开发或测试环境中运行（并解决问题）。
您可能在网上看到说Oracle 23ai新特性DBMS_HCHECK,这个说法目前来说是不对的，在oracle 23ai中DBMS_HCHECK已经改名为
DBMS_DICTIONARY_CHECK，在Oracle 23c FREE中才叫DBMS_HCHECK。
在Oracle 23ai中，提供了两个DBMS包：
DBMS_DICTIONARY_CHECK.FULL
DBMS_DICTIONARY_CHECK.CRITICAL
FULL过程执行所有检查并在屏幕上显示输出，并将其写入跟踪文件。我们可以针对根CDB或PDB数据库运行它。
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>
在运行全面检查时，您可以选择使用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>
CRITICAL过程只执行关键检查，并在屏幕上显示输出，并将其写入跟踪文件。可以针对CDB或PDB数据库运行它。
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: 没有问题。
目前Oracle 23ai正式版还没有发布，官方文档和软件都在进行细微调整可能会出现名称不一样的情况，我们也期待Oracle 23ai的正式发布，大家如果有什么见解，欢迎评论区交流！
欲知更多精彩内容，请关注下一期公众号！
原文作者：Digital Observer；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
