重新编译Invalid Oracle schema objects

2019独角兽企业重金招聘Python工程师标准>>>

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

查找Invalid objects

COLUMN owner format a15
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

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

The Manual Approach

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

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

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

select
'alter '||object_type||' '||'"'||owner||'"'||'.'||object_name||' compile;' from dba_objects 
where status='INVALID'
order by object_type;

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

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

Custom Script

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

SET SERVEROUTPUT ON SIZE 1000000
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;
/

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

DBMS_UTILITY.compile_schema

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

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

UTL_RECOMP

UTL_RECOMP包内含了两个用来处理invalid objects的procedure,"RECOMP_SERIAL"使用串行方式，"RECOMP_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-指定需要进行重新编译的schema.如果为null则DB中所有无效的对象均被重编译。

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

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

使用举例:

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
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

转载于:https://my.oschina.net/liuliufa/blog/74442