sqlplus编译失效对象

原文整理自：http://www.51testing.com/?uid-16403-action-viewspace-itemid-98161；http://www.eygle.com/archives/2005/01/ecioaeaoeeeaoea.html

先看个帖子：http://www.itpub.net/thread-1112553-1-1.html

每次数据库做了升级(打各种数据库方面的补丁后,不是数据库本身的升级,是各种对象改变增加修改等,呵呵不，别误解)
完了以后我都要查一下数据库中的失效对象，每次都发现多了好多，尤其是包啊过程啊等,都要重新再编译一次，才全部都好了，有点奇怪，为什么总需要这样呢?
大家有这样的经历把,只要重新编译就OK了，不这样做，生产中一堆失效对象,不编译估计对生产有影响吧,系统用到这些包和过程等的时候，发现失效会自己去编译吗?

跟帖解答：你修改对象的操作导致了存储过程被置于invalid 状态。因为升级有一些DDL操作，导致相关存储过程失效。可以使用自带的重编译脚本$ORACLE_HOME/rdbms/admin/utlrp.sql ，存储过程再次使用的时候自动编译。但是，自动编译可能成功也可能失败，
升级完检查失效对象是好习惯。
更好的习惯是，更改对象之前先查看会影响哪些对象

1、查找无效对象
你可以根据这个查询结果来判断使用下面的哪种方法来编译你数据库里面的无效对象.

SQL> COLUMN object_name FORMAT A30

SQL> SELECT owner,object_type,object_name,STATUS
FROM dba_objects WHERE
STATUS='INVALID' ORDER BY owner,object_type,object_name;

2、手动编译
如果无效对象的数量很少,那么你可以逐个编译这些对象.
如:

SQL> ALTER PACKAGE my_package COMPILE;
SQL> ALTER PACKAGE my_package COMPILE BODY;
SQL> ALTER PROCEDURE my_procedure COMPILE;
SQL> ALTER FUNCTION my_function COMPILE;
SQL> ALTER TRIGGER my_trigger COMPILE;
SQL> ALTER VIEW my_view COMPILE;

你也可以用DBMS_DDL包来编译(但只用于PL/SQL对象,所以你不用它来编译视图等):

SQL>  EXEC DBMS_DDL('PACKAGE','MY_SCHEMA','MY_PACKAGE'); 
SQL>  EXEC DBMS_DDL('PACKAGE BODY','MY_SCHEMA','MY_PACKAGE'); 
SQL> EXEC DBMS_DDL('PROCEDURE','MY_SCHEMA','MY_PROCEDURE'); 
SQL> EXEC DBMS_DDL('FUNCTION','MY_SCHEMA','MY_FUNCTION'); 
SQL> EXEC DBMS_DDL('TRIGGER','MY_SCHEMA','MY_TRIGGER');

3、通用脚本
在某些情况下你可能有很多无效对象要编译,这样话手工编译就显得效率太低了.
在这种情况下你可以些一个通用的脚本生成编译脚本.
下面的脚本用来查询无效的package 和package body并生成便宜这些对象的脚本.
但是这方法不会考虑ORACLE对象之间的依赖关系.

SQL> SET SERVEROUTPUT ON SIZE 1000000 
SQL> BEGIN

FOR cur_rec IN (SELECT owner,object_name,object_type,DECODE (object_type,'PACKAGE',1,'PACKAGE BODY',2,2) AS recompile_order FROM dba_objects WHERE object_type IN ('PACKAGE','PACKAGE BODY') AND STATUS!='VALID' ORDER BY 4) LOOP

BEGIN 
IF cur_rec.object_type='PACKAGE ' THEN 
EXECUTE IMMEDIATE 'ALTER '||cur_rec.object_type||' 
"'||cur_rec.owner||'"."'||cur_rec.object_name||'" COMPILE'; 
ElSE 
EXECUTE IMMEDIATE'ALTER PACKAGE 
"'||cur_rec.owner||'"."'||cur_rec.object_name||'" COMPILE BODY'; 
END IF; 
EXCEPTION 
WHEN OTHERS THEN 
DBMS_OUTPUT.put_line(cur_rec.object_type||' : '||cur_rec.owner||' : 
'||cur_rec.object_name); 
END; 
END LOOP; 
END;

/

4、使用DBMS_UTILITY.compile_schema

使用这个包将会编译指定schema下的所有procedures, functions, packages, and triggers.
你可以在sqlplus 下使用它,如:
SQL> EXEC DBMS_UTILITY.compile_schema(schema => ‘SCOTT’);

5、使用UTL_RECOMP
UTL_RECOMP包有两个存储过程:RECOMP_SERIAL 和 RECPMP_PARALLEL
从存储过程的名字可以看出一个是非并行,一个是并行方式.
使用并行方式会加快编译速度,包的定义如下:

PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

参数使用方法:
schema
- 想编译的模式,如果为NULL,将编译数据库的所有无效对象.
threads
- 并行度,如果为NULL,会使用参数job_queue_processes的值.
通常threads的值最好和CPU的数量想匹配,以发挥并行的最大优势.
flags
ORACLE内部使用的诊断测试参数.
如:

-- Schema level.EXEC UTL_RECOMP.recomp_serial('SCOTT'); 
SQL> EXEC UTL_RECOMP.recomp_parallel(4,'SCOTT'); 
-- Database level.EXEC UTL_RECOMP.recomp_serial(); 
SQL> EXEC UTL_RECOMP.recomp_parallel(4); 
-- Using job_queue_processes value.EXEC UTL_RECOMP.recomp_parallel(); 
SQL> EXEC UTL_RECOMP.recomp_parallel(NULL,'SCOTT');

UTL_RECOMP包的一些使用限制

(1).并行执行使用的是job队列.当运行并行编译的时候所有job都会被diable直到编译完成.
(2).包必须在sqlplus中以sys用户或者有sysdba权限的用户运行.
(3).UTL_RECOMP依赖于DBMS_STANDARD,DBMS_JOB,DBMS_RANDOM
(4).如果在运行这个包的时候执行DDL语句可能会导致死锁.

6、utlrp.sql 和 utlprp.sql
utlrp和utlprp脚本可以用来重编译数据库的所有无效对象.通常我们会在Oracle的升级指导中看到这个脚本，Oracle强烈推荐在migration/upgrade/downgrade之后，通过运行此脚本编译失效对象.

脚本位于$ORACLE_HOME/rdbms/admin目录.你可以看到utlrp.sql只是调用utlprp.sql, utlrp.sql在调用utlprp.sql的时候会传递给它一个参数,默认为0,这个参数其实就是并行度,其实utlprp.sql调用的是utl_recomp.recomp_parallel:

- 基于CPU_COUNT参数设置并行度.
1 - 以串行方式编译对象,一次编译一个.
N - 以N个并行度进行编译.
脚本需要以sys用户或者有sysdba权限的用户来运行，并且当时数据库中最好不要有活动事物或DDL操作，否则极容易导致死锁的出现（这是很容易理解的）

自动编译可能成功也可能失败。