To Identify And Resolve Database Objects Time stamp Mismatch

Objective: To check mismatch in Database Object Timestamp and to resolve such mismatches.

Applies to Oracle 10g Release 2, and Oracle 11g.

Script to check mismatch in Database Object Timestamp.

The following query should return no rows when object Timestamp are in sync.

      select do.obj# dobj, po.obj# pobj , p_timestamp, po.stime p_stime
      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;

If there are any object time stamp mismatch;

Run the utlirp.sql script to invalidate all plsql objects and then run utlrp.sql to recompile them all.  Scripts utlirp.sql and utlrp.sql can be found in $ORACLE_HOME/rdbms/admin and if you open up these script files you can find instructions.

Normally upon execution of utlirp.sql/utlrp.sql all the out of sync Timestamp objects should be in sync.

Doing so should avoid any ORA-4068, ORA-4065, ORA-06508.