重编译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.sql select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID'; Spool off @recompile.sql

 

包体

Spool pkg_body.sql Select '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.sql select alter materizlized view ' || owner ||'.'||object_name || ' compile;' From dba_objects where status <> 'VALID' And object_type =‘UNDEFINED’; Spool off @undefined.sql

 

java类(未测试)

Spool javaclass.sql Select 'alter java class ' || owner ||'.'||object_name || ' resolve;' from dba_objects where status <> 'VALID' And object_type ='JAVA CLASS'; Spool off @javaclass.sql

 

typebody

Spool typebody.sql Select 'alter type ' || owner ||'.'||object_name || ' compile body;' From dba_objects where status <> 'VALID' And object_type ='TYPE BODY'; Spool off @typebody.sql

 

public_synonym（未测试）

Spool public_synonym.sql Select '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