检查离线对象与失效对象

离线数据表对象检查命令如下：

SQL> SELECT DB_ID,USER_ID UID,'PROC_FUNCTON',PROC_NAME AS OBJ_NAME FROM DBA_PROCEDURES WHERE VALID='F'
UNION ALL
SELECT A.DB_ID,USER_ID UID,'INDEX',INDEX_NAME AS OBJ_NAME FROM DBA_INDEXES A INNER JOIN  DBA_TABLES B ON A.TABLE_ID=B.TABLE_ID AND A.DB_ID=B.DB_ID WHERE VALID='F'
UNION ALL
SELECT DB_ID,USER_ID UID,'VIEW',VIEW_NAME AS OBJ_NAME FROM DBA_VIEWS WHERE VALID='F'
UNION ALL
SELECT DB_ID,USER_ID UID,'TRIG',TRIG_NAME AS OBJ_NAME FROM DBA_TRIGGERS WHERE VALID='F';

如果有记录返回，则说明存在离线对象。如若有其他数据库对象，需登录至对应数据库进行查询，或者在SYSTEM系统库查询‘SYS_’开头的系统表。

系统表/系统视图中存在VALID字段，当其属性值为F，则说明该对象标识为失效，此时需要重新编译生成这个对象，或使用replace语句进行对象重建：

SQL> ALTER VIEW "QX_INSERT"."VIEW_AA" RECOMPILE;