Script:verify Oracle Object timestamp discrepancy

首先我们要知道这几个结构， 那就是    

create table obj$                                            /* object table */
( obj#          number not null,                            /* object number */
  dataobj#      number,                          /* data layer object number */
  owner#        number not null,                        /* owner user number */
  name          varchar2("M_IDEN") not null,                  /* object name */
  namespace     number not null,         /* namespace of object (see KQD.H): */
 /* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
                                                  /* 8 = LOB, 9 = DIRECTORY, */
  /* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
                                     /* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */
                                                 /* 58 = (Data Mining) MODEL */
  subname       varchar2("M_IDEN"),               /* subordinate to the name */
  type#         number not null,                 /* object type (see KQD.H): */
  /* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
             /* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
              /* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
      /* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
                                             /* 23 = DIRECTORY , 24 = QUEUE, */
    /* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
    /* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
                 /* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
                                                  /* 35 = INDEX SUBPARTITION */
                                                 /* 82 = (Data Mining) MODEL */
                                /* 92 = OLAP CUBE DIMENSION,  93 = OLAP CUBE */
                   /* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
  ctime         date not null,                       /* object creation time */
  mtime         date not null,                      /* DDL modification time */
 stime date not null, /* specification timestamp (version) */
  status        number not null,            /* status of object (see KQD.H): */
                                     /* 1 = VALID/AUTHORIZED WITHOUT ERRORS, */
                          /* 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */
                            /* 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, */
                         /* 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */

 create table dependency$                                 /* dependency table */
( d_obj#        number not null,                  /* dependent object number */
  d_timestamp   date not null,   /* dependent object specification timestamp */
  order#        number not null,                             /* order number */
  p_obj#        number not null,                     /* parent object number */
 p_timestamp date not null, /* parent object specification timestamp */
  d_owner#      number,                           /*  dependent owner number */
  property      number not null,                   /* 0x01 = HARD dependency */
                                                   /* 0x02 = REF  dependency */
                                          /* 0x04 = FINER GRAINED dependency */
  d_attrs       raw("M_CSIZ"), /* Finer grain attr. numbers if finer grained */
  d_reason      raw("M_CSIZ"))  /* Reason mask of attrs causing invalidation */

  obj$ 和dependency$ 是2个基础的数据字典表:   obj$.stime的解释是specification timestamp (version)，实际上这个字段代表了该对象的版本，指这个数据字典版本被创建的日期。 dependency$.p_timestamp代表依赖关系中父对象的数据字典版本被创建的日期。   例如STANDARD这个对象的STIME 字典版本的创建日期可能非常早：  

SQL> select name,stime,ctime,mtime from sys.obj$ where name='STANDARD';

NAME                           STIME     CTIME     MTIME
------------------------------ --------- --------- ---------
STANDARD 18-APR-06 17-SEP-11 17-SEP-11
STANDARD                       17-SEP-11 17-SEP-11 17-SEP-11

    注意若你的数据库是通过restore seed database(DBCA时选择非custom database)可能许多对象的stime=ctime。   有时候为了确保Oracle组件的正常，我们需要检验 父对象和依赖关系的数据字典版本是一致的，若不一致那么会导致组件对象失效(component object invalid)。   以下脚本可以列出数据库中所有父对象和依赖关系的数据字典版本是不一致的对象：  

set pagesize 10000
column d_name format a20
column p_name format a20
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;