Compile the method in n of an invalid object in Oracle

Common methods for compiling invalid objectsThere are some invalid objects in the database. There are many reasons for this phenomenon. The most common one is Database Upgrade (such as modifying the table structure) and migration. There are two ways to compile invalid objects: 1. Use the alter *** compile statement to compile 2. Use sysdba to execute ORACLE_HOME/rdbms/admin/utlrp. SQL script 3 is compiled using the dbms_utility package. which one to use depends on the actual situation. Query Invalid object SQL:

Select Count(*)  FromUser_objects  WhereObject_typeIn('Procedure ', 'function', 'trigger', 'view', 'package ') AndStatus = 'invalid ';

  Compile with intermediate scripts in SQL * PlusCompile the SQL * Plus script, which can help you scan illegal scripts and re-compile them: Create the script recompile. SQL

SetFeedbackOff SetHeadingOff SetLinesize 1000 SetPagesize 0 SetPauseOff SetTrimspoolOn SetVerifyOff Spool TMP. SQL; Select'Alter '| object_type | ''| Owner |'. '| object_name | 'compile ;'  FromAll_objects  WhereStatus = 'invalid' AndObject_typeIn ('Function', 'java source', 'java class', 'Procedure ', 'package', 'trigger '); Select'Alter package' | Owner | '.' | object_name | 'compile body ;'  FromAll_objects  WhereStatus = 'invalid' AndObject_type = 'package body '; SpoolOff; @ TMP. SQL

In SQL * Plus @ recompile. SQL when you run the script, the second script will be created. This script is called TMP. SQL. It publishes all the alter commands and then runs the script. Compiling PL/SQL statements Using cursor CompilationIn the above method, you can only know the cause of a compilation failure. You can use PL/SQL to implement more detailed error information.

Declare V_objname user_objects.object_name %Type; V_objtype user_objects.object_type %Type;  CursorCurIs SelectObject_name, object_type FromUser_objects WhereStatus = 'invalid' AndObject_typeIn ('Function', 'java source', 'java class', 'Procedure ', 'package', 'trigger '); Begin  OpenCur;  Loop FetchCurIntoV_objname, v_objtype; Exit WhenCur %Notfound; Begin Execute Immediate'Alter '| v_objtype | ''| v_objname | 'compile '; Dbms_output.put_line ('compile '| v_objtype | ''| v_objname |' () SUCCESS '); Exception When Others Then Dbms_output.put_line ('compile '| v_objtype | ''| v_objname |' () failed. '|Sqlerrm); End;  End Loop;  CloseCur; End;

Of course, this PL/SQL can be easily modified to procedure or function to see how you like it. The method proposed by RaymondRaymond mentioned in recompiling invalid objects how to effectively recompile invalid objects. Three effective methods (

• Use utlrp under $ ORACLE_HOME/rdbms/admin. SQL script compilation. this script is generally run after migration or upgrade. raymond said the disadvantage of this method is that this script re-compiles the objects in the whole database, so it is not advisable. some netizens pointed out that utlrp. SQL actually calls utlrcmp. in this way, you can use the utl_recomp package for SQL (this is a better method ).
• Use the dbms_utility package for compilation, but it also has some limitations.
• Raymond mentioned his solution: But someone immediately pointed out that recompilation of views is powerless (alter_compile can only process: Package, package body, procedure, function, trigger ). see the following script

Create Or Replace ProcedureRecompile_schema Is V_type user_objects.object_type %Type; V_name user_objects.object_name %Type; V_stat user_objects.status %Type;  CursorC_obj  Is SelectBase From(SelectA. object_id Base , B. object_id REL FromUser_objects , User_objects B ,Sys. Dependency $ C WhereA. object_id = c.d _ OBJ # AndB. object_id = c.p _ OBJ # AndA. object_typeIn('Package ', 'Processure ', 'Function ', 'Package body ', -- 'View ', 'Trigger ') AndB. object_typeIn('Package ', 'Processure ', 'Function ', 'Package body ', -- 'View ', 'Trigger ') AndNotA. object_name = B. object_name) Objects Connect ByBase =PriorREL GroupByBase OrderBy Max(Level)Desc; Begin  -- Loop through all objects in order of dependancy.  ForC_rowInC_obj  Loop -- Select the objects attributes (type, Name & status ). SelectObject_type , Object_name , Status IntoV_type , V_name , V_stat FromUser_objects WhereObject_id = c_row.base; -- If the object is invalid, recompile it. IfV_stat = 'invalid'Then Dbms_ddl.alter_compile (v_type,User, V_name ); End If;  End Loop;  -- Recompile all remaining invalid objects (all those without dependencies ).  ForC_rowIn(SelectObject_type , Object_name FromUser_objects WhereStatus = 'invalid' AndObject_typeIn('Package ', 'Processure ', 'Function ', 'Trigger ', 'Package body ', -- 'View ', 'Trigger '))  Loop Dbms_ddl.alter_compile (c_row.object_type,User, C_row.object_name );  End Loop; EndRecompile_schema; Rem examples Rem 1. recompileAllObjects sequentially: RemExecuteUtl_recomp.recomp_serial (); Rem Rem 2. recompile objectsIn SchemaScott sequentially: RemExecuteUtl_recomp.recomp_serial ('Scott '); Rem Rem 3. recompileAllObjectsUsing4ParallelThreads: RemExecuteUtl_recomp.recomp_parallel (4 ); Rem Rem 4. recompile objectsIn SchemaJoeUsing The Number OfThreads Rem specifiedIn TheParamter job_queue_processes: RemExecuteUtl_recomp.recomp_parallel (Null, 'Job '); Rem Rem 5. recompileAllObjectsUsing2ParallelThreads, but allow Rem other applicationsTo Use TheJobQueueConcurrently: RemExecuteUtl_recomp.recomp_parallel (2,Null, Rem utl_recomp.share_job_queue ); Rem Rem 6.Restore TheJobQueue AfterA failureInRecomp_parallel: RemExecuteUtl_recomp.restore_job_queue ();