Oracle中编译无效的对象常用方法

在数据库中，会存在一些无效的对象，导致这种现象的发生原因很多，其中最常见的就是数据库升级(例如修改了表的结构)，迁移而引起。
有两种编译无效对象的方式：

1 使用alter **** compile 语句进行编译

2 以SYSDBA用户，执行ORACLE_HOME/rdbms/admin/utlrp.sql 脚本

3 用DBMS_UTILITY包来进行编译.

具体使用哪种，根据实际情况选择。

查询无效对象SQL:

SELECT COUNT (*)
FROM user_objects
WHERE object_type IN (’PROCEDURE’,’FUNCTION’,’TRIGGER’,’VIEW’,’PACKAGE’)
    AND status = ’INVALID’;


在SQL*plus中利用中间脚本编译
编写SQL*Plus脚本，它可以帮组你扫描非法的脚本并尝试重新编译它们：
建立脚本reCompile.sql
SET feedback OFF
SET heading OFF
SET linesize 1000
SET pagesize 0
SET pause OFF
SET trimspool ON
SET verify OFF

spool tmp.sql;
SELECT ’alter ’||object_type||’ ’||owner||’.’||object_name||’ compile;’
FROM all_objects
WHERE status = ’INVALID’
    AND object_type in
        (’FUNCTION’,’JAVA SOURCE’,’JAVA CLASS’,’PROCEDURE’,’PACKAGE’,’TRIGGER’);
SELECT ’alter package ’||owner||’.’||object_name||’ compile body;’
FROM all_objects
WHERE status = ’INVALID’
    AND object_type = ’PACKAGE BODY’;
spool OFF;
@tmp.sql

在SQL*Plus中
@reCompile.sql

当你运行的时候，这个脚本将会创建第二个脚本，这个脚本叫做Tmp.sql。它发布所有的ALTER命令然后运行这个脚本。
编写PL/SQL利用游标编译
在上面的方法中，只能知道某某编译失败，不清楚失败原因，可以用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            (’FUNCTION’,’JAVA SOURCE’,’JAVA CLASS’,’PROCEDURE’,’PACKAGE’,’TRIGGER’);
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;

当然这个PL/SQL可以轻松的修改为procedure或者function，看你喜欢怎么用了。

Raymond提出的方法
Raymond 在Recompiling invalid objects提到了如何有效地重新编译无效对象.提到了三种比较有效地方法(
利用$ORACLE_HOME/rdbms/admin下的utlrp.sql脚本编译.一般都是在迁移或者升级之后运行该脚本.Raymond说该方法 的不足之处在于这个脚本是对整个数据库中的对象进行重新编译的,所以不可取.有网友指出utlrp.sql 实际上是调用utlrcmp.sql的这样就可以用utl_recomp包来做喽(这样就是比较好的方法).
用DBMS_UTILITY包来进行编译.但是也有一定的局限性.
Raymond提到了自己的解决办法: 不过也立刻有人指出来,这样对 View 的重新编译是无能为力的(ALTER_COMPILE只能处理:PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TRIGGER).参见下面的脚本
CREATE OR REPLACE PROCEDURE RECOMPILE_SCHEMA
IS
v_Type USER_OBJECTS.OBJECT_TYPE%TYPE;
v_Name USER_OBJECTS.OBJECT_NAME%TYPE;
v_Stat USER_OBJECTS.STATUS%TYPE;

CURSOR c_Obj
IS
     SELECT BASE
     FROM (SELECT A.OBJECT_ID BASE
            ,       B.OBJECT_ID REL
            FROM    USER_OBJECTS A
               ,       USER_OBJECTS B
               ,       SYS.DEPENDENCY$ C
            WHERE A.OBJECT_ID = C.D_OBJ#

AND     B.OBJECT_ID = C.P_OBJ#
            AND     A.OBJECT_TYPE IN (’PACKAGE’,
                                     ’PROCEDURE’,
                                     ’FUNCTION’,
                                     ’PACKAGE BODY’,
                                   -- ’VIEW’,
                                     ’TRIGGER’)
            AND     B.OBJECT_TYPE IN (’PACKAGE’,
                                     ’PROCEDURE’,
                                     ’FUNCTION’,
                                     ’PACKAGE BODY’,
                                   -- ’VIEW’,
                                     ’TRIGGER’)

AND     NOT A.OBJECT_NAME = B.OBJECT_NAME) OBJECTS
     CONNECT BY BASE = PRIOR REL
     GROUP    BY BASE
     ORDER    BY MAX(LEVEL) DESC;
BEGIN
-- loop through all objects in order of dependancy.
FOR c_Row IN c_Obj
LOOP
     -- select the objects attributes (type, name & status).
     SELECT OBJECT_TYPE
     ,       OBJECT_NAME
     ,       STATUS
     INTO    v_Type
     ,       v_Name
     ,       v_Stat
     FROM    USER_OBJECTS
     WHERE OBJECT_ID = c_Row.BASE;

     -- if the OBJECT is INVALID, recompile it.
     IF v_Stat = ’INVALID’ THEN
       DBMS_DDL.ALTER_COMPILE(v_Type, USER, v_Name);
     END IF;
END LOOP;

-- Recompile all remaining INVALID OBJECTS (all those without dependencies).
FOR c_Row IN ( SELECT OBJECT_TYPE
              ,       OBJECT_NAME
              FROM    USER_OBJECTS
              WHERE STATUS = ’INVALID’
              AND     OBJECT_TYPE IN (’PACKAGE’,
                                     ’PROCEDURE’,
                                     ’FUNCTION’,

’TRIGGER’,
                                     ’PACKAGE BODY’,
                                  --    ’VIEW’,
                                     ’TRIGGER’) )
LOOP
     DBMS_DDL.ALTER_COMPILE(c_Row.OBJECT_TYPE, USER, c_Row.OBJECT_NAME);
END LOOP;
END RECOMPILE_SCHEMA;

Rem    EXAMPLES
Rem       1. Recompile all objects sequentially:
Rem              execute utl_recomp.recomp_serial();
Rem
Rem       2. Recompile objects in schema SCOTT sequentially:
Rem              execute utl_recomp.recomp_serial(’SCOTT’);
Rem
Rem       3. Recompile all objects using 4 parallel threads:
Rem              execute utl_recomp.recomp_parallel(4);
Rem
Rem       4. Recompile objects in schema JOE using the number of threads
Rem          specified in the paramter JOB_QUEUE_PROCESSES:
Rem              execute utl_recomp.recomp_parallel(NULL, ’JOE’);
Rem
Rem       5. Recompile all objects using 2 parallel threads, but allow
Rem          other applications to use the job queue concurrently:

Rem              execute utl_recomp.recomp_parallel(2, NULL,
Rem                                                 utl_recomp.share_job_queue);
Rem
Rem       6. Restore the job queue after a failure in recomp_parallel:
Rem              execute utl_recomp.restore_job_queue();