p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Helvetica}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Helvetica; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 9.0px Tahoma; min-height: 11.0px}
--Oracle sys用户创建自动编译未编译成功的对象
create or replace procedure system_recomp is
sql1 user_objects%ROWTYPE;
sql2 user_objects%ROWTYPE;
sql3 user_objects%ROWTYPE;
v_sql1 varchar2(100);
v_sql2 varchar2(100);
v_sql3 varchar2(100);
begin
for sql1 in ( select * from user_objects where status='INVALID' and object_type='TRIGGER') loop
v_sql1 := 'alter trigger '||sql1.object_name||' compile';
execute immediate v_sql1;
end loop;
for sql2 in ( select * from user_objects where status='INVALID' and object_type='FUNCTION') loop
v_sql2 := 'alter FUNCTION '||sql2.object_name||' compile';
execute immediate v_sql2;
end loop;
for sql3 in ( select * from user_objects where status='INVALID' and object_type='PROCEDURE') loop
v_sql3 := 'alter PROCEDURE '||sql3.object_name||' compile';
execute immediate v_sql3;
end loop;
exception when others then
return;
end system_recomp;
/
--批量查询 失效的函数 并逐条执行：
select 'alter FUNCTION '||object_name||' compile;' from user_objects where status='INVALID' and object_type='FUNCTION';
--批量查询 失效的过程 并逐条执行：
select 'alter PROCEDURE '||object_name||' compile;' from user_objects where status='INVALID' and object_type='PROCEDURE';
--批量查询 失效的触发器 并逐条执行：
select 'alter trigger '||object_name||' compile;' from user_objects where status='INVALID' and object_type='TRIGGER';