DBA Hub

📋Steps in this guide1/2

hcheck.sql : A Quick Check for Known Data Dictionary Problems

The hckeck.sql script checks for known data dictionary problems in the Oracle database, so it can be used as a quick health check of a database when trying to diagnose problems.

oracle miscconfigurationintermediate
by OracleDba
22 views
1

Download hcheck.sql

Read the contents of the following My Oracle Support (MOS) note. Download the relevant script for your version from the MOS note. In this case we used "hcheck.sql" for version 10.2.05 onward.
2

Run hcheck.sql

Once you have the script all you need to do is connect as a user with SYSDBA privilege and run the script. In the case of multitenant architecture you will need to run the script for the root container and all PDBs, as shown below. The following example shows the output from a 12.2 PDB, so you will know what to expect. If you get any failures you can investigate and fix them. For more information see: 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
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
86
87
88
89
90
91
CONN sys@cdb1 AS SYSDBA
@hcheck.sql

CONN sys@pdb1 AS SYSDBA
@hcheck.sql

CONN sys@pdb2 AS SYSDBA
@hcheck.sql

SQL> @hcheck.sql
HCheck Version 07MAY18 on 16-JUN-2018 14:54:12
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name: CDB1
Is CDB?: YES CON_ID: 3 Container: PDB1

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp      Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- LobNotInObj                 ... 1202000100 <=  *All Rel* 06/16 14:54:12 PASS
.- MissingOIDOnObjCol          ... 1202000100 <=  *All Rel* 06/16 14:54:13 PASS
.- SourceNotInObj              ... 1202000100 <=  *All Rel* 06/16 14:54:20 PASS
.- OversizedFiles              ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- PoorDefaultStorage          ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- PoorStorage                 ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- TabPartCountMismatch        ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- OrphanedTabComPart          ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- MissingSum$                 ... 1202000100 <=  *All Rel* 06/16 14:54:24 PASS
.- MissingDir$                 ... 1202000100 <=  *All Rel* 06/16 14:54:25 PASS
.- DuplicateDataobj            ... 1202000100 <=  *All Rel* 06/16 14:54:25 PASS
.- ObjSynMissing               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- ObjSeqMissing               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedUndo                ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndex               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndexPartition      ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndexSubPartition   ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedTable               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedTablePartition      ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedTableSubPartition   ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- MissingPartCol              ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedSeg$                ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- OrphanedIndPartObj#         ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- DuplicateBlockUse           ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- FetUet                      ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- Uet0Check                   ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- SeglessUET                  ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- BadInd$                     ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- BadTab$                     ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- BadIcolDepCnt               ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- ObjIndDobj                  ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- TrgAfterUpgrade             ... 1202000100 <=  *All Rel* 06/16 14:54:27 PASS
.- ObjType0                    ... 1202000100 <=  *All Rel* 06/16 14:54:29 PASS
.- BadOwner                    ... 1202000100 <=  *All Rel* 06/16 14:54:29 PASS
.- StmtAuditOnCommit           ... 1202000100 <=  *All Rel* 06/16 14:54:29 PASS
.- BadPublicObjects            ... 1202000100 <=  *All Rel* 06/16 14:54:30 PASS
.- BadSegFreelist              ... 1202000100 <=  *All Rel* 06/16 14:54:30 PASS
.- BadDepends                  ... 1202000100 <=  *All Rel* 06/16 14:54:30 PASS
.- CheckDual                   ... 1202000100 <=  *All Rel* 06/16 14:54:31 PASS
.- ObjectNames                 ... 1202000100 <=  *All Rel* 06/16 14:54:31 PASS
.- BadCboHiLo                  ... 1202000100 <=  *All Rel* 06/16 14:54:32 PASS
.- ChkIotTs                    ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- NoSegmentIndex              ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- BadNextObject               ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- DroppedROTS                 ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- FilBlkZero                  ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- DbmsSchemaCopy              ... 1202000100 <=  *All Rel* 06/16 14:54:33 PASS
.- OrphanedIdnseqObj           ... 1202000100 >  1201000000 06/16 14:54:33 PASS
.- OrphanedIdnseqSeq           ... 1202000100 >  1201000000 06/16 14:54:33 PASS
.- OrphanedObjError            ... 1202000100 >  1102000000 06/16 14:54:34 PASS
.- ObjNotLob                   ... 1202000100 <=  *All Rel* 06/16 14:54:34 PASS
.- MaxControlfSeq              ... 1202000100 <=  *All Rel* 06/16 14:54:34 PASS
.- SegNotInDeferredStg         ... 1202000100 >  1102000000 06/16 14:54:34 PASS
.- SystemNotRfile1             ... 1202000100 >   902000000 06/16 14:54:35 PASS
.- DictOwnNonDefaultSYSTEM     ... 1202000100 <=  *All Rel* 06/16 14:54:37 PASS
.- OrphanTrigger               ... 1202000100 <=  *All Rel* 06/16 14:54:37 PASS
.- ObjNotTrigger               ... 1202000100 <=  *All Rel* 06/16 14:54:37 PASS
---------------------------------------
16-JUN-2018 14:54:37  Elapsed: 25 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)



PL/SQL procedure successfully completed.

not supported.

Complete output is in trace file:
not supported.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!