Oracle编译用户无效对象

在采用IMPDP/IMP工具迁移数据时，经常会提示无效对象的警告，需要采用如下方式处理。1. 查询指定用户的无效对象su – Oraclesqlplus / as sysdbaSQL> 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. 自动生成编译无效对象SQL1）统计当前用户无效对象数量: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 72）生成编译无效对象SQLSQL> 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更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址