编译Oracle中无效的对象的N中方法

 

编译无效的对象常用方法

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

有两种编译无效对象的方式：

 

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();