Oracle编译用户无效对象

在采用IMPDP/IMP工具迁移数据时，经常会提示无效对象的警告，需要采用如下方式处理。

1. 查询指定用户的无效对象

su – oracle

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