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;

原文：https://www.cnblogs.com/xiaogaokui/p/9001914.html