نازسازگاری در دیتادیکشنری امر متداولی نیست و به ندرت اتفاق می افتد اما در صورت رخ دادن می تواند بسیار چالش ساز باشد نمونه ای از این دست ناسازگاری که شاید شما هم با آن روبرو شده باشید، invalid reference است مثلا segmentای که اطلاعاتش در dba_objects موجود نیست.
تا قبل از نسخه 23c، این قبیل ناسازگاری ها را می توانستیم با اسکریپت hcheck.sql شناسایی کنیم. اما در اوراکل نسخه 23c، پکیجی به نام DBMS_DICTIONARY_CHECK(البته در 23.3) ارائه شد که با کمک آن می توانیم از consistency در دیتادیکشنری اطمینان حاصل کنیم. این پکیج پروسیجرهای متعددی دارد:
SQL> desc DBMS_DICTIONARY_CHECK PROCEDURE CRITICAL PROCEDURE FULL PROCEDURE DUPLICATEDATAOBJ PROCEDURE IDNSEQOBJ PROCEDURE IDNSEQSEQ PROCEDURE INDEXPARTITIONSEG PROCEDURE INDEXSEG PROCEDURE INDINDPARMISMATCH PROCEDURE INDPARTOBJ PROCEDURE LOBSEG PROCEDURE MVIEW PROCEDURE NEXTOBJECT PROCEDURE NOSEGMENTINDEX PROCEDURE OBJECTNAMES PROCEDURE OBJERROR ….
در این بین، پروسیجر FULL می تواند اطلاعات کاملی را در زمینه درستی Data Dictionary ارائه دهد که در ادامه نمونه ای از خروجی آن را مشاهده می کنید:
SQL> set serveroutput on size unlimited
SQL> exec DBMS_DICTIONARY_CHECK.FULL;
dbms_dictionary_check on 06-DEC-2023 18:25:16
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- Mview ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- ValidDir ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 12/06 18:25:16 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- UndoSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TableSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- PartCol ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- FetUet ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidInd ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidTab ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- CheckDual ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- NextObject ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- IdnseqObj ... 2300000000 > 1201000000 12/06 18:25:17 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 12/06 18:25:17 PASS
.- ObjError ... 2300000000 > 1102000000 12/06 18:25:17 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 12/06 18:25:17 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 12/06 18:25:17 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
.- LobSeg ... 2300000000 <= *All Rel* 12/06 18:25:17 PASS
---------------------------------------
06-DEC-2023 18:25:17 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
PL/SQL procedure successfully completed.
در ادامه قصد داریم ناسازگاری ای را در دیتادیکشنری ایجاد کنیم و سپس این پکیج را اجرا کنیم بدین منظور، رکوردی را از جدول seq$ حذف می کنیم(اعمال تغییر در جداول دیتادیکشنری ممکن است منجر به نابودی دیتابیس شود!):
SQL> select obj# from seq$ where rownum=1;
OBJ#
----------
130
SQL> delete seq$ where OBJ#=130;
1 row deleted.
SQL> commit;
Commit complete.
DBMS_DICTIONARY_CHECK را مجددا اجرا می کنیم:
SQL> exec DBMS_DICTIONARY_CHECK.full;
dbms_dictionary_check on 06-DEC-2023 18:31:40
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- LobNotInObj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- SourceNotInObj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- OversizedFiles ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- PoorDefaultStorage ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- PoorStorage ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- TabPartCountMismatch ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- TabComPartObj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- Mview ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- ValidDir ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- DuplicateDataobj ... 2300000000 <= *All Rel* 12/06 18:31:40 PASS
.- ObjSyn ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjSeq ... 2300000000 <= *All Rel* 12/06 18:31:41 WARN
HCKW-0006: SEQ$ entry missing for OBJ$ type#=6 (Doc ID 1360524.1)
OBJ#=130 Name=SYS.UGROUP_SEQUENCE
.- UndoSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndexSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndexPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndexSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TableSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TablePartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TableSubPartitionSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- PartCol ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IndPartObj ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DuplicateBlockUse ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- FetUet ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- Uet0Check ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- SeglessUET ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidInd ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidTab ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IcolDepCnt ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjIndDobj ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- TrgAfterUpgrade ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjType0 ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidOwner ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- StmtAuditOnCommit ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- PublicObjects ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- SegFreelist ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidDepends ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- CheckDual ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjectNames ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ChkIotTs ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- NoSegmentIndex ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- NextObject ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DroppedROTS ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- FilBlkZero ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DbmsSchemaCopy ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- IdnseqObj ... 2300000000 > 1201000000 12/06 18:31:41 PASS
.- IdnseqSeq ... 2300000000 > 1201000000 12/06 18:31:41 PASS
.- ObjError ... 2300000000 > 1102000000 12/06 18:31:41 PASS
.- ObjNotLob ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- SegNotInDeferredStg ... 2300000000 > 1102000000 12/06 18:31:41 PASS
.- SystemNotRfile1 ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- DictOwnNonDefaultSYSTEM ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ValidateTrigger ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- ObjNotTrigger ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 12/06 18:31:41 PASS
.- OBJRecycleBin ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
.- LobSeg ... 2300000000 <= *All Rel* 12/06 18:31:41 PASS
---------------------------------------
06-DEC-2023 18:31:41 Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
PL/SQL procedure successfully completed.
همانطور که می بینید خطای مربوط به object شماره 130 توسط این پکیج نمایش داده شده است.
HCKW-0006: SEQ$ entry missing for OBJ$ type#=6 (Doc ID 1360524.1) OBJ#=130 Name=SYS.UGROUP_SEQUENCE
یکی دیگر از پروسیجرهای مهم این پکیج، CRITICAL است:
SQL> set serveroutput on size unlimited
SQL> exec DBMS_DICTIONARY_CHECK.CRITICAL;
dbms_dictionary_check on 06-DEC-2023 18:36:01
----------------------------------------------
Catalog Version 23.0.0.0.0 (2300000000)
db_name: FREE
Is CDB?: YES CON_ID: 1 Container: CDB$ROOT
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg ... 2300000000 <= *All Rel* 12/06 18:36:01 PASS
.- MaxControlfSeq ... 2300000000 <= *All Rel* 12/06 18:36:01 PASS
.- InvalidTSMaxSCN ... 2300000000 > 1202000000 12/06 18:36:01 PASS
---------------------------------------
06-DEC-2023 18:36:01 Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_6267_DICTCHECK.trc
PL/SQL procedure successfully completed.
وحید یوسف زاده
ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com