Automatic compilation of failed Oracle database objects

Yesterday saw a post about the failure of the object recompilation problem, and then found themselves in the company also appeared inexplicable failure object.

--Create an automatic compilation invalidation process transaction log table declare tabcnt integer: = 0;begin Select COUNT (*) into tabcnt from dba_tables where Table_name= ' Recompi  Le_log ';  If tabcnt = 0 Then execute immediate ' CREATE TABLE Recompile_log (Rdate date,errmsg varchar2 (200)) ';  End if;end;/--creates a stored procedure that compiles a failed object create or replace procedure recompile_invalid_objects as Str_sql varchar2 (200);   --The SQL statement used in the middle P_owner varchar2 (20);     --owner name, schema errm VARCHAR2 (200); --Intermediate error message begin/*****************************************************/P_owner: = ' owner ';/*** User name ****************** //*****************************************************/INSERT INTO Recompile_log (rdate, errmsg) VALUES (     Sysdate, ' time to recompile invalid objects '); --Compile the fail stored procedure for invalid_procedures in (select object_name from all_objects where status = ' Invalid ' and object_type = ' PROCEDURE ' and Owner=upper (P_owner)) loop str_sql: = ' alter PROCEDURE ' | | Invalid_procedures.object_name | |    ' Compile ';    Begin execute Immediate str_sql; ExcEption when Others and begin ERRM: = ' ERROR by obj: ' | | invalid_procedures.object_name| | ' ' | |        SQLERRM;      Insert into Recompile_log (rdate, ErrMsg) VALUES (SYSDATE,ERRM);    End  End    End Loop; --Compiler invalidation function for invalid_functions in (select object_name from all_objects where status = ' Invalid ' and object_type = ' fun Ction ' and Owner=upper (P_owner)) loop str_sql: = ' alter function ' | | Invalid_functions.object_name | |    ' Compile ';    Begin execute Immediate str_sql; Exception when Others and begin ERRM: = ' ERROR by obj: ' | | invalid_functions.object_name| | ' ' | |        SQLERRM;      Insert into Recompile_log (rdate, ErrMsg) VALUES (SYSDATE,ERRM);    End  End  End Loop; --Compile invalidation package for invalid_packages in (select object_name from all_objects where status = ' Invalid ' and object_type = ' Packa GE ' and Owner=upper (P_owner)) loop str_sql: = ' alter package ' | | Invalid_packages.object_name | |    ' Compile '; Begin execute Immediate str_sql;   Exception when Others and begin ERRM: = ' ERROR by obj: ' | | invalid_packages.object_name| | ' ' | |        SQLERRM;      Insert into Recompile_log (rdate, ErrMsg) VALUES (SYSDATE,ERRM);    End  End    End Loop; --Compile failure type for invalid_types in (select object_name from all_objects where status = ' Invalid ' and object_type = ' type ' A nd owner=upper (p_owner)) loop str_sql: = ' alter type ' | | Invalid_types.object_name | |    ' Compile ';    Begin execute Immediate str_sql; Exception when Others and begin ERRM: = ' ERROR by obj: ' | | invalid_types.object_name| | ' ' | |        SQLERRM;      Insert into Recompile_log (rdate, ErrMsg) VALUES (SYSDATE,ERRM);    End  End  End Loop;  --Compile the fail index for Invalid_indexs in (select object_name from all_objects where status = ' Invalid ' and object_type = ' index ' and Owner=upper (P_owner)) loop str_sql: = ' alter index ' | | Invalid_indexs.object_name | |    ' Rebuild ';    Begin execute Immediate str_sql; Exception whenOthers THEN begin ERRM: = ' ERROR by obj: ' | | invalid_indexs.object_name| | ' ' | |        SQLERRM;      Insert into Recompile_log (rdate, ErrMsg) VALUES (SYSDATE,ERRM);    End  End  End Loop; --Compile fail trigger for invalid_triggers in (select object_name from all_objects where status = ' Invalid ' and object_type = ' TRI Gger ' and Owner=upper (P_owner)) loop str_sql: = ' alter TRIGGER ' | | Invalid_triggers.object_name | |    ' Compile ';    Begin execute Immediate str_sql; Exception when Others and begin ERRM: = ' ERROR by obj: ' | | invalid_triggers.object_name| | ' ' | |        SQLERRM;      Insert into Recompile_log (rdate, ErrMsg) VALUES (SYSDATE,ERRM);    End  End End Loop;  end;/--creates a task schedule that performs the task every 8 o'clock in the morning, and guarantees that the task has only one declare jobcnt integer: = 0;  Job_recompile number: = 0; Str_sql varchar2 (n); Begin select COUNT (*) into jobcnt from all_jobs where what = ' recompile_invalid_objects; ' and Brok  En = ' N '; If jobcnt > 0 then to Jobs in (select job from All_jobs where what = ' recompile_invalid_objects; ' and broken = ' N ') loop str_sql: = ' begin Dbms_job.remove (' | | jobs.job| | ');      end; ';      Begin execute Immediate str_sql;      Exception when Others and then null;    End   End Loop;  End If;  --Create a task plan Dbms_job.submit (Job_recompile, ' recompile_invalid_objects; ', Sysdate, ' TRUNC (sysdate + 1) + 8/24 '); --Launch Mission plan Dbms_job.run (job_recompile); end;/

　　

Automatic compilation of failed Oracle database objects