Oracle 23ai新特性—DBMS_DICTIONARY_CHECK

在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.CRITICAL

DBMS_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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值