health check reports problem: dependency$ p_timestamp mismatch for valid objects (文档 id 781959.1)
applies to:
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***
symptoms
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
.
cause
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
solution
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;