Oracle生產中跑批預存程序或函數失效原因分析以及解決方案

標籤：iar   ocs   分享圖片   immediate   nil   wmi   pgp   taf   win   

 

Oracle生產中跑批預存程序或函數失效原因分析以及解決方案:

報錯資訊:

原因分析:

1.當我們編譯預存程序或函數時,該過程或函數引用的所有Oracle對象都將記錄在資料字典中。

該過程就依賴於這些儲存的對象。我們可以看到在資料字典中顯示了標誌為非法的有編譯錯誤的子程式。

同樣,如果一個DDL操作運行在其所相關的對象上時，儲存子程式也將是非法的。當對象變更時，其相關的對象就會變成非法對象。

如果所有的對象都在同一個資料庫中的話，則相關的對象將會在底層對象變更的同時進入非法狀態。由於資料字典在不斷地跟蹤對象間的相關，所以這種變化可以快速反應出來。

2.為什麼在遠程調用下的過程看起來有所不同呢？

答案就在於資料字典並不跟蹤遠程相關對象。實際上，由於遠程對象可能位於不同的資料庫中，因此要將所有相關遠程對象作廢實際上是不可能的（如果遠程對象處於無效期的話，資料字典可能無法對其進行訪問）。與上不同的是，遠程對象的合法性要在運行時進行檢查。

報錯情境

1.過程所引用的對象失效,例如:表結構變更

2.運行過程中dblink出問題

 

查看預存程序狀態:

# select t1.owner,t1.object_name,t1.object_type,t1.status,t1.created,t1.last_ddl_time from all_objects t1 where t1.owner = ‘XIAOGAOKUI‘ and t1.object_type = ‘PROCEDURE‘ and t1.status = ‘INVALID‘

查看過程引用的對象

# select t2.owner,t2.name,t2.type,t2.referenced_owner,t2.referenced_name from all_dependencies t2 where t2.owner = ‘XIAOGAOKUI‘ order by 2;

查看編譯過程中的報錯資訊

#select * from all_errors;

 

編譯INVALID過程

方式一:

在Oracle sqlplus中

# spool compile_invalid_porc.sql --記錄重新編譯語句

# select ‘ALTER PROCEDURE‘ || t1.object_name || ‘ COMPILE;‘ from all_objects t1 where t1.status = ‘INVALID‘ and t1.object_type = ‘PROCEDURE‘ and t1.owner = ‘XIAOGAOKUI‘

# spool off

# @compile_invalid_porc.sql

 

方式二:

create or replace procedure compite_invalid_procedures(

p_owner varchar2 --所有者名稱,即schema

)

as

--編譯使用者下invalid過程

v_sql_statement varchar2(2000);

begin

for invalid_proc in (select t1.object_name as object_name from all_objects t1 where t1.owner = upper(p_owner) and t1.object_type = ‘PROCEDURE‘ and t1.status = ‘INVALIED‘)

loop

v_sql_statement := ‘ALTER PROCEDURE‘ || invalid_proc.object_name || ‘COMPILE‘;

begin

execute immediate v_sql_statement

exception

when others then

dbms_output.put_line(sqlcode || sqlerrm);

end;

end loop;

end;

/

 

生產中解決方案:

在調用預存程序之前,增加對過程的編譯語句:

方法一:

# EXECUTE IMMEDIATE ‘ALTER PROCEDURE COMPILE_INVALID_PROCEDURES COMPILE‘;

方法二:

建立一個預存程序,在需要的時候執行,或者建立一個定時任務exec dbms_job.submit(:job_id,‘timer_auto_recompile_objs;‘,sysdate,‘sysdate+1/24‘);定時執行。

create or replace procedure timer_auto_recompile_objs

as cursor objects_list is select object_name,object_type from user_objects where status=‘INVALID‘;

begin for v_object in objects_list loop

if v_object.object_type=‘PROCEDURE‘

then execute immediate ‘alter procedure ‘||v_object.object_name||‘ compile‘;

elseif v_object.object_type=‘FUNCTION‘

then execute immediate ‘alter function ‘||v_object.object_name||‘ compile‘;

elsif v_object.object_type=‘VIEW‘

then execute immediate ‘alter view ‘||v_object.object_name||‘ compile‘;

elsif v_object.object_type=‘MATERIALIZED VIEW‘

then execute immediate ‘alter materialized view ‘||v_object.object_name||‘ compile‘;

end if;

end loop;

end;

 

附錄:

編譯過程：

alter procedure New_procedure compile ;

為了能夠執行此命令，需要擁有這個過程，或者具有alter any procedure系統許可權。

 

編譯函數：

alter function New_function compile ;

為了能夠執行，需要擁有此函數，或者具有alter any procedure系統許可權。

 

編譯包：

alter package [user.]package_name compile  [package|body];

為了能夠執行，需要擁有此包，或者具有alter any procedure系統許可權。

 

替換：

可以使用各自的Create or replace命令來替換過程，函數和包。

使用or replace子句，保留了這些對象已被賦予的許可權。

 

刪除：

刪除過程：drop procedure                 New_procedure;

刪除函數：drop function                   New_functioin;

刪除包：  drop package                     New_package;

刪除包體：drop package body           New_package;

 

 

 

 

 

Oracle生產中跑批預存程序或函數失效原因分析以及解決方案