health check reports problem: dependency$ p_timestamp mismatch for valid objects (文档 id 781959.1)

oracle database - enterprise edition - version 9.2.0.5 to 12.1.0.2 [release 9.2 to 12.1]
information in this document applies to any platform.
***checked for relevance on 18-jan-2014***

health check output reports

problem: dependency$ p_timestamp mismatch for valid objects?
may be ok - needs checking, (warning: [w], error: [e]).

[w] - p_obj#=37817 d_obj#=38416 
[w] - p_obj#=38014 d_obj#=38605 
[w] - p_obj#=38020 d_obj#=38611 
[w] - p_obj#=38043 d_obj#=38634 
[w] - p_obj#=38061 d_obj#=38652 
[w] - p_obj#=38064 d_obj#=38655 
[w] - p_obj#=38087 d_obj#=38678 
[w] - p_obj#=38090 d_obj#=38681

.

this issue is caused due to mismatch in timestamp information with the dependent objects.

it may not be an actual dictionary corruption.

this can cause problems during upgrade and plsql validation

please note : its advisable to run the script during non business hours or when the load in the database is less.

the solution is applicable only for d_type with value 5(synonyms) returned  from the below query

select

do.obj# d_obj,do.name d_name, do.type# d_type,

po.obj# p_obj,po.name p_name,

to_char(p_timestamp,'dd-mon-yyyy hh24:mi:ss') "p_timestamp",

to_char(po.stime ,'dd-mon-yyyy hh24:mi:ss') "stime",

decode(sign(po.stime-p_timestamp),0,'same','*differ*') x

from sys.obj$ do, sys.dependency$ d, sys.obj$ po

where p_obj#=po.obj#(+)

and d_obj#=do.obj#

and do.status=1 /*dependent is valid*/

and po.status=1 /*parent is valid*/

and po.stime!=p_timestamp /*parent timestamp not match*/

order by 2,1;

check if the d_type is reported as 5.

compile the synonyms using the following script

set pagesize 10000 
set trimspool on 
set head off 
set echo off 
spool /tmp/compiling.lst 

select 'alter public synonym "'||synonym_name||'" compile;' 
from dba_synonyms where owner='public'; 
select 'alter synonym "'||owner||'"."'||synonym_name||'" compile;' 
from dba_synonyms where owner!='public'; 

sql> spool off

now run this spooled file

sql> spool /tmp/compiling_result.lst

sql>@/tmp/compiling.lst

sql> spool off

now re-run the following query and check if the number of objects have reduced as compared to output

select

do.obj# d_obj,do.name d_name, do.type# d_type,

po.obj# p_obj,po.name p_name,

to_char(p_timestamp,'dd-mon-yyyy hh24:mi:ss') "p_timestamp",

to_char(po.stime ,'dd-mon-yyyy hh24:mi:ss') "stime",

decode(sign(po.stime-p_timestamp),0,'same','*differ*') x

from sys.obj$ do, sys.dependency$ d, sys.obj$ po

where p_obj#=po.obj#(+)

and d_obj#=do.obj#

and do.status=1 /*dependent is valid*/

and po.status=1 /*parent is valid*/

and po.stime!=p_timestamp /*parent timestamp not match*/

order by 2,1;