Oracle Database 23ai 中的DBMS_HCHECK
在 Oracle 23ai 中，DBMS_HCHECK 包允许我们检查数据库中已知的数据字典问题。
几年前，Oracle 发布了 hcheck.sql 脚本（文档 ID 136697.1）来检查数据库中已知的数据字典问题。 DBMS_HCHECK 包意味着我们不再需要下载 hcheck.sql 脚本来执行此操作。需要 hcheck.sql 脚本可以留言。
存储过程中的参数，FULL 程序进行详尽的检查，而 CRITICAL 选项仅专注于最基本的检查，使其执行速度更快。这些过程最有价值的方面之一是它们不仅能够识别问题，而且还能够修复问题（如果您设置了 Repair= TRUE 参数）。
关于结果的说明
执行结果报告如下：
- CRITICAL：需要立即关注并修复。
- FAIL：需要解决的高优先级问题。
- WARN：不紧急但值得解决。
- PASS：未检测到问题。
注意：在 Oracle 23c 中，此包称为 DBMS_HCHECK，而在早期版本（如 Oracle 23.3ai）中，它称为 DBMS_DICTIONARY_CHECK。
以下实验环境为Oracle 23ai，采用的DBMS_DICTIONARY_CHECK。
一、使用脚本检查数据字典
1.1 检查cdb
SQL> spool hcheck.log
SQL> @hcheck
HCheck Version 04AUG23 on 19-DEC-2024 16:20:29
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOTCatalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- MissingOIDOnObjCol ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- OrphanedTabComPart ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- MissingSum$ ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- MissingDir$ ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 12/19 16:20:29 PASS
.- ObjSynMissing ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- ObjSeqMissing ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedUndo ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedIndex ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedIndexPartition ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedIndexSubPartition ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedTable ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedTablePartition ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedTableSubPartition ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- MissingPartCol ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedSeg$ ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedIndPartObj# ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- FetUet ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadInd$ ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadTab$ ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadIcolDepCnt ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadOwner ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadPublicObjects ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadSegFreelist ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadDepends ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- CheckDual ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- BadNextObject ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanedIdnseqObj ... 2300000000 > 1201000000 12/19 16:20:30 PASS
.- OrphanedIdnseqSeq ... 2300000000 > 1201000000 12/19 16:20:30 PASS
.- OrphanedObjError ... 2300000000 > 1102000000 12/19 16:20:30 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 12/19 16:20:30 PASS
.- SystemNotRfile1 ... 2300000000 > 902000000 12/19 16:20:30 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- OrphanTrigger ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 12/19 16:20:30 PASS
---------------------------------------
19-DEC-2024 16:20:30 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Note that hcheck has been run on CDB$ROOT, consider to run it in each PDBPL/SQL procedure successfully completed.Statement processed.Complete output is in trace file:
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11626_HCHECK.trcSQL> spool off1.2 检查pdb
conn sys/oracle@pdb1 as sysdbaspool hcheck.log@hcheck
HCheck Version 04AUG23 on 19-DEC-2024 16:23:01
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- MissingOIDOnObjCol ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedTabComPart ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- MissingSum$ ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- MissingDir$ ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- ObjSynMissing ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- ObjSeqMissing ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedUndo ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedIndex ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedIndexPartition ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedIndexSubPartition ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedTable ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedTablePartition ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedTableSubPartition ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- MissingPartCol ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedSeg$ ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedIndPartObj# ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- FetUet ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadInd$ ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadTab$ ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadIcolDepCnt ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadOwner ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadPublicObjects ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadSegFreelist ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadDepends ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- CheckDual ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- BadNextObject ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- OrphanedIdnseqObj ... 2300000000 > 1201000000 12/19 16:23:02 PASS
.- OrphanedIdnseqSeq ... 2300000000 > 1201000000 12/19 16:23:02 PASS
.- OrphanedObjError ... 2300000000 > 1102000000 12/19 16:23:02 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 12/19 16:23:02 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/19 16:23:02 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 12/19 16:23:02 PASS
.- SystemNotRfile1 ... 2300000000 > 902000000 12/19 16:23:02 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 12/19 16:23:02 PASS
.- OrphanTrigger ... 2300000000 <= *All Rel* 12/19 16:23:02 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 12/19 16:23:02 PASS
---------------------------------------
19-DEC-2024 16:23:02 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)PL/SQL procedure successfully completed.Statement processed.Complete output is in trace file:
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11647_HCHECK.trcspool off二、DBMS_DICTIONARY_CHECK.FULL
FULL 过程执行所有检查并在屏幕上显示输出，并将其写入跟踪文件。我们可以针对根容器或可插拨数据库运行它。
以pdb1为示例
conn sys/oracle@pdb1 as sysdbaspool hcheck.logexec dbms_dictionary_check.full
HCheck Version 04AUG23 on 19-DEC-2024 16:23:01
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 3 Container: FREEPDB1Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- MissingOIDOnObjCol ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 12/19 16:23:01 PASS
。。省略
---------------------------------------
19-DEC-2024 16:23:02 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)PL/SQL procedure successfully completed.Statement processed.Complete output is in trace file:
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11647_HCHECK.trcspool off
三、DBMS_DICTIONARY_CHECK.CRITICAL
CRITICAL 过程仅执行关键检查并在屏幕上显示输出，并将其写入跟踪文件。我们可以针对根容器或可插拨数据库运行它。
以pdb1为示例
conn sys/oracle@pdb1 as sysdbaSQL> set serveroutput on size unlimited
SQL> exec DBMS_DICTIONARY_CHECK.CRITICAL;
dbms_dictionary_check on 19-DEC-2024 16:38:52
----------------------------------------------
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_11647_DICTCHECK.trcCatalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg ... 2300000000 <= *All Rel* 12/19 16:38:52 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/19 16:38:52 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 12/19 16:38:52 PASS
.- SysSequences ... 2300000000 <= *All Rel* 12/19 16:38:52 PASS
---------------------------------------
19-DEC-2024 16:38:52 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11647_DICTCHECK.trcPL/SQL procedure successfully completed.
SQL>四、DBMS_DICTIONARY_CHECK.FULL(REPAIR=TRUE)
下面介绍了如何修复数据字典的问题或不一致。
EXECUTE dbms_dictionary_check.full(repair=>TRUE)
dbms_dictionary_check on 19-DEC-2024 16:40:01
----------------------------------------------
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_11647_DICTCHECK.trcCatalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 12/19 16:40:01 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 12/19 16:40:01 PASS
。。省略
.- ObjLogicalConstraints ... 2300000000 <= *All Rel* 12/19 16:40:01 PASS
.- SysSequences ... 2300000000 <= *All Rel* 12/19 16:40:01 PASS
.- ValidateFile ... 2300000000 <= *All Rel* 12/19 16:40:01 PASS
---------------------------------------
19-DEC-2024 16:40:01 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11647_DICTCHECK.trcPL/SQL procedure successfully completed.