Execution Environment:
Access Privileges:
Requires to be run connected as SYS schema
Usage:
$ sqlplus
SQL*Plus: Release 9.2.0.2.0 - Production on Mon Nov 11 12:00:06 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production
SQL> set serveroutput on
SQL> spool outputfile
SQL> execute hcheck.full Instructions: 1. Connect as SYS schema in sqlplus
2. Create package hOut as described in Note:101468.1
3. Create package hcheck in SYS schema (Refer the attachment under SCRIPT to Create package hcheck
4. spool outputfile
5. execute hcheck.full 6. Output will go to the spool file and the session trace file. The script will report various dictionary related issues which may or may not be a problem - Any problems reported should be reviewed by an experienced support analyst as some reported "problems" may be normal and expected.
hout.sql
hcheck.sql
sample:
SQL> @hout
Package created.
No errors.
Package body created.
SQL> @hcheck
Package created.
No errors.
Package body created.
No errors.
HCheck Version 8i-11/1.60
Problem: Duplicate DATAOBJ#, may be valid under the following:
- Using Transportable Tablespaces
- OBJ's belong to different tablespaces
DATAOBJ#=51667 OBJ#=52267 Name=HR.REGIONS Type#=2
DATAOBJ#=51671 OBJ#=52272 Name=HR.LOCATIONS Type#=2
DATAOBJ#=51674 OBJ#=52277 Name=HR.DEPARTMENTS Type#=2
DATAOBJ#=51677 OBJ#=52280 Name=HR.JOBS Type#=2
DATAOBJ#=51709 OBJ#=52305 Name=OE.WAREHOUSES Type#=2
DATAOBJ#=51717 OBJ#=52314 Name=OE.ORDER_ITEMS Type#=2
DATAOBJ#=51721 OBJ#=52319 Name=OE.ORDERS Type#=2
DATAOBJ#=51725 OBJ#=52327 Name=OE.PRODUCT_INFORMATION Type#=2
DATAOBJ#=51729 OBJ#=52333 Name=OE.PROMOTIONS Type#=2
Problem: Dependency$ p_timestamp mismatch for VALID objects
May be Ok - needs checking, (Warning: [W], Error: [E]).
[E] - P_OBJ#=52850 D_OBJ#=52851
Found 10 potential problems and 0 warnings
Contact Oracle Support with the output
to check if the above needs attention or not
PL/SQL procedure successfully completed.
© 2000 – 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
Identify Data Dictionary Inconsistency
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 10.2.0.4 – Release: 8.1.7 to 10.2
Information in this document applies to any platform.
Purpose
This document was created to help analyst how to identify inconsistency in data dictionary.
Last Review Date
August 29, 2007
Instructions for the Reader
Troubleshooting Details
Data Dictionary Corruption
Common dd corruptions are:
1. Data Dictionary Inconsistency, missing rows in tables:
– Tab$/Ind$ with no entries in OBJ$
- Undo$/Tab$/Ind$ with no entries in SEG$
- Seg$ with no entries in TAB$/IND$/OBJ$
2. Missing data dictionary objects
3. Corrupted data dictionary objects (table, index, or table-index inconsistency)
4. Invalid entries in data dictionary tables.
1. Identifying Objects with Data Dictionary Inconsistency
In order to detect data dictionary inconsistency we need to run hcheck.full procedure, see Note 136697.1.
a. Connect as SYS schema in sqlplus
b. Create package hOut as described in Note 101468.1
c. Create package hcheck in SYS schema as described in Note 136697.1 attachment.
d. set serveroutput on
c. execute hcheck.full
The script will report various dictionary related issues that may or may not be a problem.
Any problems reported should be reviewed by an experienced support analyst as some
reported “problems” may be normal and expected.
Example of HCHECK.FULL output:
ORPHAN IND$: OBJ=200449 DOBJ=200449 TS=0 RFILE/BLOCK=0 0 BO#=200446 SegType=
^- May be OK. Needs manual check
ORPHAN IND$: OBJ=39442 DOBJ=39442 TS=14 RFILE/BLOCK=2 49 BO#=39438 SegType=
Problem: Orphaned TAB$ (no SEG$)
ORPHAN TAB$: OBJ=1817074 DOBJ=0 TS=0 RFILE/BLOCK=0 0 BOBJ#= SegType=
^- May be OK. Needs manual check
ORPHAN TAB$: OBJ=2149126 DOBJ=2149126 TS=19 RFILE/BLOCK=31 44291 BOBJ#= SegType=
Problem: Orphaned SEG$ Entry
ORPHAN SEG$: SegType=INDEX TS=20 RFILE/BLOCK=33 28435
Based on the hcheck.full output you will have to identify the objects that show a dd inconsistency, and verify the reported inconsistency.
Select name,type# from obj$ where obj#=<OBJ>; /* 1=INDEX, 2=TABLE, 3=CLUSTER, 21=LOB, 25=IOT
Select object_name,owner,object_type from dba_objects where object_id=<OBJ>;
Some of the problems, mainly the one marked as ’May be OK. Needs manual check ‘ could be a false alarm.
Check the type of the object.
Lob Index on temporary table or IOT do not have a segment, than the problem message is a false alarm.