重编译Oracle中的invalid对象

(环境Oracle 9i，所有语句在sqlplus下执行)

 

1、invalid对象的查询

查询invalid对象的总个数

 select count(*) from dba_objects where status='INVALID'; 
分类查询invalid对象的个数

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
查询对象的详细信息
select owner, object_name,object_type from dba_objects where status =’INVALID’;

 

2、手动重新编译
重编译语句的格式：

Alter procedure <owner>.<procedure_name> compile;Alter function <owner>.<function_name> compile;Alter view <owner>.<view_name> compile;Alter package <owner>.<package_name> compile;Alter package <owner>.<package_name> compile body;Alter materialized view <owner>.<Package_name> Compile;

 

使用脚本编译

视图、包等主要对象：

Spool recompile.sqlselect 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';Spool [e-mail]

 

包体

Spool pkg_body.sqlSelect 'alter package '||object_type|| ' ' || owner ||'.'||object_name || ' compile body;' From dba_objects where status <> 'VALID' And object_type = 'PACKAGE BODY';Spool off@pkg_body.sql

 

未定义对象（未测试）

Spool undefined.sqlselect alter materizlized view ' || owner ||'.'||object_name || ' compile;' From dba_objects where status <> 'VALID' And object_type =‘UNDEFINED’;Spool [e-mail]

 

java类(未测试)

Spool javaclass.sqlSelect 'alter java class ' || owner ||'.'||object_name || ' resolve;' from dba_objects where status <> 'VALID' And object_type ='JAVA CLASS';Spool [e-mail]

 

typebody

Spool typebody.sqlSelect 'alter type ' || owner ||'.'||object_name || ' compile body;' From dba_objects where status <> 'VALID' And object_type ='TYPE BODY';Spool [e-mail]

 

public_synonym（未测试）

Spool public_synonym.sqlSelect 'alter public synonym ' || owner ||'.'||object_name || ' compile;' From dba_objects Where status <> 'VALID' And owner = 'PUBLIC' And object_type = 'SYNONYM';Spool off@public_synonym.sql

 

3、若编译出错
若重编译时，出现编译错误时，使用"show error"查看错误，针对错误再进行处理

 

 

在SDE中，也可以尝试先移动包，再重新生成：SQL> DROP PACKAGE BODY sde.sde_util;SQL> DROP PACKAGE sde.sde_util;SQL> commit;SQL> exit;Navigate to the $SDEHOME/lib directory, log into SQL*Plus as the SDE user then execute the following commands :SQL> start SDE_UTIL.sps;SQL> start SDE_UTIL.spb;SQL> commit;

 

4、其它解决方法
使用dbms_dll.alter_compile
exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

使用DBMS_UTILITY.COMPILE_SCHEMA
exec dbms_utility.compile_schema('SCOTT');

使用UTLRP.SQL
@utlrp.sql