Re: A tale of data dictionary corruption in Oracle10gR2

Hamid,

You might want to check that the timestamps for dependency$ and obj$ match. If you had aborted compilation on an APPS database, I believe this mismatch has been known to occur. If it did occur, you will get all sorts of strange ORA-600 errors. You should use the query below to determine this list and "fix" the dependency$.timestamp column manually.

SELECT DISTINCT NAME , o.owner#
FROM obj$ o, dependency$ d
WHERE o.stime <> d.p_timestamp
AND o.ctime <= d.p_timestamp
AND o.obj# = d.p_obj#

I speak from the experience of helping a colleague recover from such a disaster. We had Oracle support's blessings as it was a P1 TAR. The fix was to run the following:

update dependency$
set p_timestamp = (select stime from obj$ o where o.obj# = dependency$.p_obj#)
where d_obj# = <Objid, one by one for the affected ones>

Do this for SYS first. Shutdown abort and startup and repeat for others.... YMMV!

-- 
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is always inevitable; Discouragement is invariably optional

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

--
http://www.freelists.org/webpage/oracle-l