create or replace procedure P_RECOMPILE_PROCEDURE(po_fhz out varchar2,
po_msg out varchar2) is cursor cur_object is
SELECT 'ALTER ' || DECODE(OBJECT_TYPE,
'PACKAGE BODY',
'PACKAGE',
'PROCEDURE',
'PROCEDURE',
OBJECT_TYPE) || ' ' || OWNER || '.' ||
OBJECT_NAME || ' COMPILE' AS STRSQL
FROM ALL_OBJECTS t
WHERE STATUS = 'INVALID'
AND t.object_type in ('PACKAGE BODY', 'PROCEDURE')
AND OWNER in ('user')
order by t.owner desc;
begin for cur in cur_object loop
begin
execute immediate cur.STRSQL;
end;
end loop;
commit;end P_RECOMPILE_PROCEDURE;
--
这是我写的存储过程用于重新编译失效的存储过程,在点击执行后就一直在运行,使用pl/sql工具执行的,请指教!
po_msg out varchar2) is cursor cur_object is
SELECT 'ALTER ' || DECODE(OBJECT_TYPE,
'PACKAGE BODY',
'PACKAGE',
'PROCEDURE',
'PROCEDURE',
OBJECT_TYPE) || ' ' || OWNER || '.' ||
OBJECT_NAME || ' COMPILE' AS STRSQL
FROM ALL_OBJECTS t
WHERE STATUS = 'INVALID'
AND t.object_type in ('PACKAGE BODY', 'PROCEDURE')
AND OWNER in ('user')
order by t.owner desc;
begin for cur in cur_object loop
begin
execute immediate cur.STRSQL;
end;
end loop;
commit;end P_RECOMPILE_PROCEDURE;
--
这是我写的存储过程用于重新编译失效的存储过程,在点击执行后就一直在运行,使用pl/sql工具执行的,请指教!
'PACKAGE BODY',
'PACKAGE',
'PROCEDURE',
'PROCEDURE',
OBJECT_TYPE) || ' ' || OWNER || '.' ||
OBJECT_NAME || ' COMPILE' AS STRSQL
FROM ALL_OBJECTS t
WHERE STATUS = 'INVALID'
AND t.object_type in ('PACKAGE BODY', 'PROCEDURE')
AND OWNER in ('user')
AND ROWNUM < 2
order by t.owner desc;
http://www.codeidea.com/blog
§20.4 无效对象的编译
Oracle的许多对象,包括视图、触发器、存储过程等,由于某些系统的相关性,经常使一些对象变为无效。为了使这些无效对象变为有效,可以使用下面语句来实现无效对象的自动编译。REM*********************************************************
REM File : mkcomp.sql (for PLSQL)
REM Function : This PLSQL script creates a script for the
REM compilation of all invalid objects of the
REM current user and starts the script after
REM creation
REM Usage : SQL @mkcomp.sql
REM Author : Frank PUECHL
REM*********************************************************SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET PAGESIZE 0SPOOL compobj.sqlSELECT 'SET FEEDBACK ON' FROM DUAL;SELECT 'PROMPT Compile VIEW ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER VIEW ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'VIEW'
ORDER BY OBJECT_NAME;SELECT 'PROMPT Compile TRIGGER ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER TRIGGER ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'TRIGGER'
ORDER BY OBJECT_NAME;SELECT 'PROMPT Compile PROCEDURE ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER PROCEDURE ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME;SELECT 'PROMPT Compile FUNCTION ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER FUNCTION ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'FUNCTION'
ORDER BY OBJECT_NAME;SELECT 'PROMPT Compile PACKAGE ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE;' || CHR(10)
|| 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'PACKAGE'
ORDER BY OBJECT_NAME;SELECT 'PROMPT Compile PACKAGE BODY ' || OBJECT_NAME || ' ...' || CHR(10) ||
'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;'
|| CHR(10) || 'SHOW ERRORS;' || CHR(10) || 'PROMPT;'
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
AND OBJECT_TYPE = 'PACKAGE BODY'
ORDER BY OBJECT_NAME;SPOOL OFFSET TERMOUT ON
SET ECHO OFF
SET FEEDBACK OFFSELECT '>>> Invalid objects of current user'
FROM DUAL;SELECT OBJECT_TYPE || ': ' || OBJECT_NAME
FROM USER_OBJECTS
WHERE STATUS <> 'VALID'
ORDER BY OBJECT_TYPE, OBJECT_NAME;SELECT '>>> Total: ' || COUNT(*)
FROM USER_OBJECTS
WHERE STATUS <> 'VALID';START compobj.sql如果不是对整个系统的所有无效对象进行编译,则可以按照下面办法进行处理:例1:编译无效视图:Alter view emp_v COMPILE;例2:编译无效存储过程和函数:Alter PROCEDURE emp_p COMPILE;例3:编译无效包和包体:Alter PACKAGE emp_b COMPILE BODY;
Alter PACKAGE emp_b COMPILE PACKAGE;
应该不会编译它自己,这个过程既然可以运行,状态就不会是invalid,sql应该取不到它。 不放心可以显式加上object_name 不等于过程名的限制。
--
--自动编译所有无效对象
is
cursor cur_invalid_objects is
select distinct t2.object_name,t2.object_type
from user_objects t2,
( select distinct d.name
from user_dependencies d,
user_objects t1
where 1=1
and t1.status='INVALID'
and d.referenced_name = t1.object_name
and d.referenced_type = t1.object_type
) t3
where 1=1
and t2.status='INVALID'
and t3.name(+)=t2.object_name
and t3.name is null;
rec_columns cur_invalid_objects%ROWTYPE;
err_status NUMERIC;
invalid_count number;
pre_invalid_count number;
begin
dbms_output.enable(100000);
pre_invalid_count:=9999999;
select count(*) into pre_invalid_count
from user_objects
where status='INVALID';
loop
invalid_count:=0;
open cur_invalid_objects;
loop
fetch cur_invalid_objects into rec_columns;
EXIT WHEN cur_invalid_objects%NOTFOUND;
begin
if upper(rec_columns.object_type)='VIEW' then
execute immediate 'alter '||rec_columns.object_type||' '||rec_columns.object_name||' compile';
else
dbms_ddl.alter_compile(rec_columns.object_type,NULL,rec_columns.object_name);
end if;
invalid_count:=invalid_count+1;
-- dbms_output.put_line
-- ('Recompiling ' || rec_columns.object_type || ' ' || rec_columns.object_name);
exception
when others then
err_status:=SQLCODE;
-- dbms_output.put_line(' Recompilation failed : '|| rec_columns.object_type || ' ' || rec_columns.object_name || SQLERRM(err_status)); null;
end;
end loop;
close cur_invalid_objects;
select count(*) into invalid_count
from user_objects
where status='INVALID';
exit when invalid_count=pre_invalid_count or invalid_count=0;
pre_invalid_count:=invalid_count;
end loop;
end P_TOOLS_COMPILE_ALL_INVALID;