重新编译Invalid Oracle schema objects

在Oracle上进行一些操作例如：upgrades,patches和DDL操作会导致某些schema objects的失效。Oracle是提供了在需要调用时自动重新编译的功能，但是有时这会比较耗时（可能会引发某些锁），特别是针对一些有复杂依赖关系的对象。另外在某些特定的环境中，它的这种自动重新编译的机制是不会被触发的，例如我在DG上查询某张视图。这样一来，就有必要主动对invalid objects进行检查与重编译。

查找Invalid objects

1	COLUMN owner format a15

2	COLUMN object_name FORMAT A30

3	SELECT owner,

4	object_type,

5	object_name,

6

status

7	FROM   dba_objects

8	WHERE  status = 'INVALID'

9	ORDER BY owner, object_type, object_name;

有数种方法来处理这些失效的对象。

The Manual Approach

如果失效对象较少而且发生的频率也比较小，可以使用手工处理，处理语句如下：

1	ALTER PACKAGE my_package COMPILE;

2	ALTER PACKAGE my_package COMPILE BODY;

3	ALTER PROCEDURE my_procedure COMPILE;

4	ALTER FUNCTION my_function COMPILE;

5	ALTER TRIGGER my_trigger COMPILE;

6	ALTER VIEW my_view COMPILE;

可以使用如下简单的语句生成上述格式的查询语句(不包含所有对象类型)：

1

select

2	'alter '||object_type||' '||'"'||owner||'"'||'.'||object_name||' compile;' from dba_objects

3	where status='INVALID'

4	order by object_type;

除了使用"ALTER"命令以外，还可以使用"DBMS_DDL"来操作，不过有一些限制，其被操作的对象只能是PL/SQL对象，所以不能使用它来对视图对象操作。

1	EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');

2	EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');

3	EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');

4	EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');

5	EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

Custom Script

面对较多的失效对象而又需要很快的完成重新编译的任务，编写脚本来完成肯定会比上述的方法要好的多。例如使用如下的脚本来查找、重编译packages与package bodies.

01	SET SERVEROUTPUT ON SIZE 1000000

02

BEGIN

03	FOR cur_rec IN (SELECT owner,

04	object_name,

05	object_type,

06	DECODE(object_type, 'PACKAGE', 1,

07	'PACKAGE BODY', 2, 2) AS recompile_order

08	FROM   dba_objects

09	WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')

10	AND    status != 'VALID'

11	ORDER BY 4)

12

LOOP

13

BEGIN

14	IF cur_rec.object_type = 'PACKAGE' THEN

15	EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||

16	' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';

17

ElSE

18	EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||

19	'"."' || cur_rec.object_name || '" COMPILE BODY';

20

END IF;

21

EXCEPTION

22	WHEN OTHERS THEN

23	DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||

24	' : ' || cur_rec.object_name);

25

END;

26

END LOOP;

27

END;

28

/

此脚本在执行过程中可能会存在对某些对象进行多次编译的情况，所以选择使用Oracle提供的现成的方法可能更好一些。

DBMS_UTILITY.compile_schema

compile_schema包含在DBMS_UTILITY包中，用来编译被指定schema中所有的procedures,functions,packages,triggers.例如：

1	EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

UTL_RECOMP包内含了两个用来处理invalid objects的procedure,"RECOMP_SERIAL"使用串行方式，"RECOMP_PARALLEL"以并行方式处理：

1	PROCEDURE RECOMP_SERIAL(

2	schema   IN   VARCHAR2    DEFAULT NULL,

3	flags    IN   PLS_INTEGER DEFAULT 0);

4

5	PROCEDURE RECOMP_PARALLEL(

6	threads  IN   PLS_INTEGER DEFAULT NULL,

7	schema   IN   VARCHAR2    DEFAULT NULL,

8	flags    IN   PLS_INTEGER DEFAULT 0);

参数说明：

schema-指定需要进行重新编译的schema.如果为null则DB中所有无效的对象均被重编译。

thread-此参数对于并行处理有效，如果为null则会使用"job_queue_processes"参数值，通常threads的值最好和CPU的数量相匹配。

flag-用来内部诊断与测试。

使用举例:

01	-- Schema level.

02	EXEC UTL_RECOMP.recomp_serial('SCOTT');

03	EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

04

05	-- Database level.

06	EXEC UTL_RECOMP.recomp_serial();

07	EXEC UTL_RECOMP.recomp_parallel(4);

08

09	-- Using job_queue_processes value.

10	EXEC UTL_RECOMP.recomp_parallel();

11	EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');

使用这个包的一些限制：

(1).并行执行使用的是job队列.当运行并行编译的时候所有job都会标记为disble直到编译完成.

(2).包必须在sqlplus中以sys用户或者有sysdba权限的用户运行.

(3).UTL_RECOMP依赖于DBMS_STANDARD,DBMS_JOB,DBMS_RANDOM.

(4).如果在运行这个包的时候执行DDL语句可能会导致死锁.

utlrp.sql and utlprp.sql

这两个SQL脚本用来处理DB中所有的invalid objects.它们通常在DB升级或者patches以后运行。它们的存放位置在$ORACLE_HOME/rdbms/admin。其实utlrp.sql只是简单调用了utlprp.sql,它在调用utlprp.sql时会传递给它一个参数,默认为0,这个参数其实用来指定并行值。

此参数值定义：

0 - 基于CPU_COUNT参数设置并行值.

1 - 以串行方式编译对象,一次编译一个.

N - 以N个线程数并行进行编译.

两个脚本需要以sys用户或者有sysdba权限的用户执行。

------------------------------------------------------------------------------------------------------------------------------------------

参考来源：

http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php

http://bbs.chinaunix.net/thread-3643482-1-1.html

http://topic.csdn.net/u/20110111/10/7b9a9e0c-5708-4146-b210-4f0d1e2f0fcd.html?53458

http://blog.csdn.net/a9529lty/article/details/6004334

http://dbataj.blogspot.tw/2007/08/how-to-compile-invalid-objects.html

http://www.51testing.com/?uid-16403-action-viewspace-itemid-98161