一、編譯無效的物件常用方法

在資料庫中，會存在一些無效的物件，導致這種現象的發生原因很多，其中最常見的就是資料庫升級(例如修改了表的結構)，遷移而引起。

有兩種編譯無效物件的方式：

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

文章轉自：http://blog.csdn.net/z3h/archive/2007/09/18/1790378.aspx非常感謝作者分享

(轉)編譯Oracle中無效的物件的N中方法

相關文章