Blog
03 de fevereiro de 2026
Garantindo a Integridade do Dicionário de Dados do Oracle com o DBMS_HCHECK - 23/26ai
O pacote DBMS_HCHECK é uma nova funcionalidade disponível a partir do Oracle Database 23 ai, que executa verificações internas de segurança no dicionário de dados do Oracle, detectando inconsistências e estruturas que possam estar corrompidas. Esses problemas no dicionário podem causar falhas em processos e, em alguns casos, até a queda da instância do banco de dados. Eles também podem se apresentar como erros internos, como o ORA-00600.
O DBMS_HCHECK ajuda a identificar essas inconsistências e, em alguns casos, oferece orientações para corrigi-las, evitando falhas no banco de dados.
A utilização do pacote DBMS_HCHECK é bastante simples e intuitiva e pode ser feita com a opção FULL ou CRITICAL. O FULL realiza uma verificação exaustiva, enquanto a opção CRITICAL se concentra apenas nas verificações mais essenciais, o que a torna mais rápida de executar.
O resultado da verificação é apresentado em 4 status:
- CRITICAL - Requer uma correção imediata.
- FAIL - Requer uma solução prioritária.
- WARN - Pode ser resolvido com mais tempo.
- PASS - Não foram encontrados problemas.
Sempre que algo diferente de PASS for encontrado, é recomendado que seja aberto um chamado no suporte da Oracle para avaliação e confirmação da ação necessária para correção.
Exemplo Prático
A verificação deve ser feita a nível de CDB e PDB. Para executar uma verificação FULL, é usado o comando: execute dbms_dictionary_check.full
SQL> set serveroutput on size unlimited SQL> execute dbms_dictionary_check.full dbms_dictionary_check on 21-APR-2025 15:44:55 ---------------------------------------------- Catalog Version 23.0.0.0.0 (2300000000) db_name: LAMIMDB Is CDB?: YES CON_ID: 1 Container: CDB$ROOT Trace File: /u01/app/oracle/diag/rdbms/lamimdb_77n_gru/lamimdb/trace/lamimdb_ora_86056_DICTC HECK.trc Catalog Fixed Procedure Name Version Vs Release Timestamp Result ------------------------------ ... ---------- -- ---------- -------------- ------ .- OIDOnObjCol ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- LobNotInObj ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- SourceNotInObj ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- OversizedFiles ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- PoorDefaultStorage ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- PoorStorage ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- TabPartCountMismatch ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- TabComPartObj ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- Mview ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- ValidDir ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- DuplicateDataobj ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- ObjSyn ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- ObjSeq ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- ValidateSeg ... 2300000000 <= *All Rel* 04/21 15:44:55 PASS .- UndoSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- IndexSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- IndexPartitionSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- TableSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- TablePartitionSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- TableSubPartitionSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- PartCol ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- IndPartObj ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- DuplicateBlockUse ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- FetUet ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- Uet0Check ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- SeglessUET ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ValidInd ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ValidTab ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- IcolDepCnt ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ObjIndDobj ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- TrgAfterUpgrade ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ObjType0 ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ValidOwner ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- StmtAuditOnCommit ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- PublicObjects ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- SegFreelist ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ValidDepends ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- CheckDual ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ObjectNames ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ChkIotTs ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- NoSegmentIndex ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- NextObject ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- DroppedROTS ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- FilBlkZero ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- DbmsSchemaCopy ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- IdnseqObj ... 2300000000 > 1201000000 04/21 15:44:56 PASS .- IdnseqSeq ... 2300000000 > 1201000000 04/21 15:44:56 PASS .- ObjError ... 2300000000 > 1102000000 04/21 15:44:56 PASS .- ObjNotLob ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- MaxControlfSeq ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- SegNotInDeferredStg ... 2300000000 > 1102000000 04/21 15:44:56 PASS .- SystemNotRfile1 ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ValidateTrigger ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ObjNotTrigger ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- InvalidTSMaxSCN ... 2300000000 > 1202000000 04/21 15:44:56 PASS .- OBJRecycleBin ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- LobSeg ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ObjLogicalConstraints ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- SysSequences ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ValidateFile ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS .- ValidateObjStub ... 2300000000 <= *All Rel* 04/21 15:44:56 PASS --------------------------------------- 21-APR-2025 15:44:56 Elapsed: 1 secs --------------------------------------- Found 0 potential problem(s) and 0 warning(s) Trace File: /u01/app/oracle/diag/rdbms/lamimdb_77n_gru/lamimdb/trace/lamimdb_ora_86056_DICTC HECK.trc PL/SQL procedure successfully completed. SQL>
Já a verificação CRITICAL é realizada com o comando execute dbms_dictionary_check.critical.
SQL> SQL> set serveroutput on size unlimited SQL> execute dbms_dictionary_check.critical dbms_dictionary_check on 21-APR-2025 15:50:18 ---------------------------------------------- Catalog Version 23.0.0.0.0 (2300000000) db_name: LAMIMDB Is CDB?: YES CON_ID: 1 Container: CDB$ROOT Trace File: /u01/app/oracle/diag/rdbms/lamimdb_77n_gru/lamimdb/trace/lamimdb_ora_86056_DICTC HECK.trc Catalog Fixed Procedure Name Version Vs Release Timestamp Result ------------------------------ ... ---------- -- ---------- -------------- ------ .- UndoSeg ... 2300000000 <= *All Rel* 04/21 15:50:18 PASS .- MaxControlfSeq ... 2300000000 <= *All Rel* 04/21 15:50:18 PASS .- InvalidTSMaxSCN ... 2300000000 > 1202000000 04/21 15:50:18 PASS .- SysSequences ... 2300000000 <= *All Rel* 04/21 15:50:18 PASS --------------------------------------- 21-APR-2025 15:50:18 Elapsed: 0 secs --------------------------------------- Found 0 potential problem(s) and 0 warning(s) Trace File: /u01/app/oracle/diag/rdbms/lamimdb_77n_gru/lamimdb/trace/lamimdb_ora_86056_DICTC HECK.trc PL/SQL procedure successfully completed. SQL>
Vale destacar que algumas correções podem ser feitas automaticamente pelo pacote, utilizando o parâmetro repair=>TRUE.
SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE).
SQL> SQL> EXECUTE dbms_dictionary_check.full(repair=>TRUE) dbms_dictionary_check on 21-APR-2025 15:51:42 ---------------------------------------------- Catalog Version 23.0.0.0.0 (2300000000) db_name: LAMIMDB Is CDB?: YES CON_ID: 1 Container: CDB$ROOT Trace File: /u01/app/oracle/diag/rdbms/lamimdb_77n_gru/lamimdb/trace/lamimdb_ora_86056_DICTC HECK.trc Catalog Fixed Procedure Name Version Vs Release Timestamp Result ------------------------------ ... ---------- -- ---------- -------------- ------ .- OIDOnObjCol ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- LobNotInObj ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- SourceNotInObj ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- OversizedFiles ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- PoorDefaultStorage ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- PoorStorage ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- TabPartCountMismatch ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- TabComPartObj ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- Mview ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ValidDir ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- DuplicateDataobj ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ObjSyn ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ObjSeq ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ValidateSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- UndoSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- IndexSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- IndexPartitionSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- TableSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- TablePartitionSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- TableSubPartitionSeg ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- PartCol ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- IndPartObj ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- DuplicateBlockUse ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- FetUet ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- Uet0Check ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- SeglessUET ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ValidInd ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ValidTab ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- IcolDepCnt ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ObjIndDobj ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- TrgAfterUpgrade ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ObjType0 ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ValidOwner ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- StmtAuditOnCommit ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- PublicObjects ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- SegFreelist ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ValidDepends ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- CheckDual ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ObjectNames ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- ChkIotTs ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- NoSegmentIndex ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- NextObject ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- DroppedROTS ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- FilBlkZero ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- DbmsSchemaCopy ... 2300000000 <= *All Rel* 04/21 15:51:42 PASS .- IdnseqObj ... 2300000000 > 1201000000 04/21 15:51:42 PASS .- IdnseqSeq ... 2300000000 > 1201000000 04/21 15:51:42 PASS .- ObjError ... 2300000000 > 1102000000 04/21 15:51:42 PASS .- ObjNotLob ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- MaxControlfSeq ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- SegNotInDeferredStg ... 2300000000 > 1102000000 04/21 15:51:43 PASS .- SystemNotRfile1 ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- ValidateTrigger ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- ObjNotTrigger ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- InvalidTSMaxSCN ... 2300000000 > 1202000000 04/21 15:51:43 PASS .- OBJRecycleBin ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- LobSeg ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- ObjLogicalConstraints ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- SysSequences ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- ValidateFile ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS .- ValidateObjStub ... 2300000000 <= *All Rel* 04/21 15:51:43 PASS --------------------------------------- 21-APR-2025 15:51:43 Elapsed: 1 secs --------------------------------------- Found 0 potential problem(s) and 0 warning(s) Trace File: /u01/app/oracle/diag/rdbms/lamimdb_77n_gru/lamimdb/trace/lamimdb_ora_86056_DICTC HECK.trc PL/SQL procedure successfully completed. SQL>
https://docs.oracle.com/en/database/oracle/oracle-database/23/admin/diagnosing-and-resolving-problems.html#GUID-D82CB4E7-B3DD-4C29-9F94-E80B4E859D1E
Postado por:
Jhonata Lamim.
385 visualizações
