DBA Hub

📋Steps in this guide1/2

DBMS_HCHECK in Oracle Database 23ai/26ai

In Oracle 23ai/26ai the DBMS_HCHECK package allows us to check for known data dictionary problems in the database.

oracle 23configurationintermediate
by OracleDba
14 views
1

DBMS_HCHECK.FULL

The procedure performs all checks and displays the output on the screen, and writes it to a trace file. We can run it against the root container or a pluggable database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
--conn sys/SysPassword1@//localhost:1521/free as sysdba
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba


SQL> set serveroutput on size unlimited
SQL> exec dbms_hcheck.full
dbms_hcheck on 26-JUL-2023 19:16:19
----------------------------------------------
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_11866_HCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- OIDOnObjCol                 ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- LobNotInObj                 ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- SourceNotInObj              ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- OversizedFiles              ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- PoorDefaultStorage          ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- PoorStorage                 ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- TabPartCountMismatch        ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- TabComPartObj               ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- Mview                       ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- ValidDir                    ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- DuplicateDataobj            ... 2300000000 <=  *All Rel* 07/26 19:16:19 PASS
.- ObjSyn                      ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ObjSeq                      ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- UndoSeg                     ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- IndexSeg                    ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- IndexPartitionSeg           ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- IndexSubPartitionSeg        ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- TableSeg                    ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- TablePartitionSeg           ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- TableSubPartitionSeg        ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- PartCol                     ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ValidSeg                    ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- IndPartObj                  ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- DuplicateBlockUse           ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- FetUet                      ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- Uet0Check                   ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- SeglessUET                  ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ValidInd                    ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ValidTab                    ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- IcolDepCnt                  ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ObjIndDobj                  ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- TrgAfterUpgrade             ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ObjType0                    ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ValidOwner                  ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- StmtAuditOnCommit           ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- PublicObjects               ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- SegFreelist                 ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ValidDepends                ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- CheckDual                   ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ObjectNames                 ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ChkIotTs                    ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- NoSegmentIndex              ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- NextObject                  ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- DroppedROTS                 ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- FilBlkZero                  ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- DbmsSchemaCopy              ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- IdnseqObj                   ... 2300000000 >  1201000000 07/26 19:16:20 PASS
.- IdnseqSeq                   ... 2300000000 >  1201000000 07/26 19:16:20 PASS
.- ObjError                    ... 2300000000 >  1102000000 07/26 19:16:20 PASS
.- ObjNotLob                   ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- SegNotInDeferredStg         ... 2300000000 >  1102000000 07/26 19:16:20 PASS
.- SystemNotRfile1             ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- DictOwnNonDefaultSYSTEM     ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ValidateTrigger             ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- ObjNotTrigger               ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 07/26 19:16:20 PASS
.- OBJRecycleBin               ... 2300000000 <=  *All Rel* 07/26 19:16:20 PASS
---------------------------------------
26-JUL-2023 19:16:20  Elapsed: 1 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11866_HCHECK.trc

PL/SQL procedure successfully completed.

SQL>
2

DBMS_HCHECK.CRITICAL

The procedure performs only critical checks and displays the output on the screen, and writes it to a trace file. We can run it against the root container or a pluggable database. For more information see: - DBMS_HCHECK - hcheck.sql : A Quick Check for Known Data Dictionary Problems Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--conn sys/SysPassword1@//localhost:1521/free as sysdba
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba


SQL> set serveroutput on size unlimited
SQL> execute dbms_hcheck.critical
dbms_hcheck on 26-JUL-2023 19:19:29
----------------------------------------------
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_11866_HCHECK.trc

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- UndoSeg                     ... 2300000000 <=  *All Rel* 07/26 19:19:29 PASS
.- MaxControlfSeq              ... 2300000000 <=  *All Rel* 07/26 19:19:29 PASS
.- InvalidTSMaxSCN             ... 2300000000 >  1202000000 07/26 19:19:29 PASS
---------------------------------------
26-JUL-2023 19:19:29  Elapsed: 0 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
Trace File: /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_11866_HCHECK.trc

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!