oracle exec编译失效,Oracle 编译用户无效对象

一、查询无效对象的方法：

1.统计失效对象

select owner, object_type, status, count(*)

from dba_objects

where status='INVALID'

group by owner, object_type, status

order by owner, object_type

2.查看具体失效对象

col owner for a20;

col object_name for a32;

col object_type for a16

col status for a8

select owner, object_name, object_type, status

from dba_objects

where status='INVALID'

order by 1, 2,3;

column owner format a15

column object_name format a25

column object_type format a15

SELECT owner,

object_name,

object_type,

status

FROM dba_objects

WHERE status != 'VALID'

AND owner != 'SYS'

AND owner != 'SYSTEM';

3.查看失效对象的依赖关系

col owner for a30

col name for a30

col type for a18

col referenced_owner for a20

col referenced_name for a30

col referenced_type for a18

col dependency_type for a4

set line 300

select OWNER,NAME,TYPE,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE,DEPENDENCY_TYPE from dba_dependencies where OWNER='SCOTT' AND NAME='V_TEST';

二、编译无效对象的方法：

1.使用ALTER * COMPLIE语句手工进行编译，这个适用于少数、个别对象失效

1)生成手动编译无效对象脚本

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','SYNONYM','JAVA SOURCE','JAVA CLASS');

2)手动执行

alter package . compile;

alter package . compile body;

alter view . compile;

alter trigger compile;

2.执行@$ORACLE_HOME/rdbms/admin/utlrp.sql脚本编译数据库失效对象。

许多情况下，由于数据库的升级或迁移，会导致数据库中的对象失效。由于对象之间可能存在复杂的倚赖关系，所以手工编译通常无法顺利通过。通常我们会在Oracle的升级指导中看到这个脚本，Oracle强烈推荐在migration/upgrade/downgrade之后，通过运行此脚本编译失效对象。但是注意,Oracle提醒，此脚本需要用SQLPLUS以SYSDBA身份运行，并且当时数据库中最好不要有活动事物或DDL操作，否则极容易导致死锁的出现。

另外，utlrp.sql 里面其实调用了$ORACLE_HOME/rdbms/admin/utlrcmp.sql来编译失效对象https://www.cndba.cn/leo1990/article/3180https://www.cndba.cn/leo1990/article/3180https://www.cndba.cn/leo1990/article/3180

3.ORACLE提供了自动编译的接口dbms_utility.compile_schema(user,false); 调用这个过程就会编译所有失效的过程、函数、触发器、包

exec dbms_utility.compile_schema( 'SCOTT' );

4.在SQL*plus中利用中间脚本编译

https://www.cndba.cn/leo1990/article/3180

set heading off;

set feedback off;

set echo off;

Set lines 999;

Spool run_invalid.sql

select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'

from dba_objects

where status = 'INVALID'

and OWNER = 'APPS'

and object_type in ('PACKAGE','FUNCTION','PROCEDURE','TRIGGER','JAVA SOURCE','JAVA CLASS','VIEW','SYNONYM');

select 'alter package ' || owner || '.' || object_name ||

' compile body;'

from dba_objects

where status = 'INVALID'

and OWNER = 'APPS'

and object_type in ('PACKAGE BODY');

spool off;

set heading on;

set feedback on;

set echo on;

@run_invalid

2)在SQL*Plus中执行

格式：@文件所在本地路径/文件名

注：路径不能有中文

SQL>@/home/oracle/reCompile.sql;

备注：运行脚本reCompile.sql的时候，会创建另一个脚本run_invalid，紧跟着执行该脚本，完成编译工作

https://www.cndba.cn/leo1990/article/3180https://www.cndba.cn/leo1990/article/3180

5.编写PL/SQL利用游标编译

DECLARE

v_objname user_objects.object_name%TYPE;

v_objtype user_objects.object_type%TYPE;

CURSOR cur IS

SELECT object_name,object_type

FROM USER_OBJECTS

WHERE status = 'INVALID'

AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW','SYNONYM','JAVA SOURCE','JAVA CLASS');

BEGIN

OPEN cur;

LOOP

FETCH cur into v_objname, v_objtype;

EXIT WHEN cur%NOTFOUND;

BEGIN

EXECUTE Immediate 'alter ' || v_objtype || ' ' || v_objname||' Compile';

dbms_output.put_line('编译' || v_objtype || ' ' || v_objname || '()成功');

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('编译' || v_objtype ||' ' || v_objname || '()失败.' || SQLERRM);

END;

END LOOP;

CLOSE cur;

END;

/

注意：视图，存储过程，函数、包等，如果代码本身没有什么错误，只是引用的对象发生了变化。也会失效。但并不影响调用，因为ORACLE在调用时会自动重新编译的，如果其它对象变化后导致编译有错误。这时调用时重新编译后也是错误并处于失效状态，所以调用会出错。

https://www.cndba.cn/leo1990/article/3180

https://www.cndba.cn/leo1990/article/3180

https://www.cndba.cn/leo1990/article/3180

版权声明：本文为博主原创文章，未经博主允许不得转载。