在采用IMPDP/IMP工具迁移数据时，经常会提示无效对象的警告，需要采用如下方式处理。
1. 查询指定用户的无效对象
sqlplus / as sysdba
SQL> select owner,object_name,replace(object_type,' ','') object_type,to_char(created,'yyyy-mm-dd') as created,to_char(last_ddl_time,'yyyy-mm-dd') as last_ddl_time,status from dba_objects where status='INVALID' and owner='HKJJW';?
OWNER? ? ? OBJECT_NAME? ? ? ? ? ? ? ? ? ? OBJECT_TYPE? ? CREATED? ? LAST_DDL_T STATUS? ?
---------- ------------------------------ --------------- ---------- ---------- -------? ?
HKJJW? ? ? FUNC_RETURN_PY? ? ? ? ? ? ? ? FUNCTION? ? ? ? 2013-08-02 2013-08-02 INVALID? ?
HKJJW? ? ? MONITORING_FLOW_PARALLEL_VIEW? VIEW? ? ? ? ? ? 2013-08-02 2013-08-02 INVALID? ?
HKJJW? ? ? TEST? ? ? ? ? ? ? ? ? ? ? ? ? VIEW? ? ? ? ? ? 2013-08-02 2013-08-02 INVALID
指定查询某用户无效对象。
SQL> select * from dba_objects where status<>'VALID' and owner='HKJJW';
2. 手动执行编译或
ALTER FUNCTION HKJJW.FUNC_RETURN_PY COMPILE;?
ALTER VIEW HKJJW.MONITORING_FLOW_PARALLEL_VIEW COMPILE;? ?
ALTER VIEW HKJJW.TEST COMPILE;
如果是index的话,需要重建.?
alter index index_name rebuild;
如果是package body?
alter package DBMS_CUBE? compile body;
3. 自动生成编译无效对象SQL
1) 统计当前用户无效对象数量:
SQL> select owner,object_type,status,count(*) from dba_objects where status='INVALID' group by owner,object_type,status order by owner,object_type;
OWNER? ? ? ? ? ? ? ? ? ? ? ? ? OBJECT_TYPE? ? ? ? STATUS? ? COUNT(*)?
------------------------------ ------------------- ------- ----------? ?
HNADEV_GROUP? ? ? ? ? ? ? ? ? FUNCTION? ? ? ? ? ? INVALID? ? ? ? ? 1? ?
HNADEV_GROUP? ? ? ? ? ? ? ? ? PACKAGE BODY? ? ? ? INVALID? ? ? ? 35? ?
HNADEV_GROUP? ? ? ? ? ? ? ? ? VIEW? ? ? ? ? ? ? ? INVALID? ? ? ? ? 7
2) 生成编译无效对象SQL
SQL> select? 'ALTER ' || OBJECT_TYPE || ' ' ||? OWNER || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status = 'INVALID' and object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW') ;
'ALTER'||OBJECT_TYPE||''||OWNER||'.'||OBJECT_NAME||'COMPILE;'?
----------------------------------------------------------------------? ?
ALTER VIEW HNADEV_GROUP.V_E_T_FEEMONTH_CAL COMPILE;? ?
ALTER VIEW HNADEV_GROUP.V_E_T_FEEMONTH COMPILE;? ?
ALTER VIEW HNADEV_GROUP.V_E_Z_BILL_STAY_GROUP COMPILE;? ?
略...? ?
通过复制以上SQL语句,直接手动执行编译执行.
4. 也可以采用如下方式在oracle用户下进行手工编译
# su - oracle?
$ sqlplus / as sysdba? ?
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql