在Oracle上进行一些操作例如:upgrades,patches和DDL操作会导致某些schema objects的失效。Oracle是提供了在需要调用时自动重新编译的功能,但是有时这会比较耗时(可能会引发某些锁),特别是针对一些有复杂依赖关系的对象。另外在某些特定的环境中,它的这种自动重新编译的机制是不会被触发的,例如我在DG上查询某张视图。这样一来,就有必要主动对invalid objects进行检查与重编译。
查找Invalid objects
1 | COLUMN owner format a15 |
2 | COLUMN object_name FORMAT A30 |
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; |
可以使用如下简单的语句生成上述格式的查询语句(不包含所有对象类型):
2 | 'alter ' ||object_type|| ' ' || '"' ||owner|| '"' || '.' ||object_name|| ' compile;' from dba_objects |
除了使用"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 |
03 | FOR cur_rec IN ( SELECT owner, |
06 | DECODE(object_type, 'PACKAGE' , 1, |
07 | 'PACKAGE BODY' , 2, 2) AS recompile_order |
09 | WHERE object_type IN ( 'PACKAGE' , 'PACKAGE BODY' ) |
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' ; |
18 | EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || |
19 | '"."' || cur_rec.object_name || '" COMPILE BODY' ; |
23 | DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || |
24 | ' : ' || cur_rec.object_name); |
此脚本在执行过程中可能会存在对某些对象进行多次编译的情况,所以选择使用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); |
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-用来内部诊断与测试。
使用举例:
02 | EXEC UTL_RECOMP.recomp_serial( 'SCOTT' ); |
03 | EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT' ); |
06 | EXEC UTL_RECOMP.recomp_serial(); |
07 | EXEC UTL_RECOMP.recomp_parallel(4); |
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