Oracle recompile failed pair image

Recompile the failed pair like executable Utlrp.sql file:

Sql>@?/Rdbms/Admin/Utlrp.sqlTIMESTAMP
--------------------------------------------------------------------------------Comp_timestamp UTLRP_BGN .- ,- -  -:Geneva: theDOC>The following PL/SQL Block invokes Utl_recomp toRecompile Invalid DOC>ObjectsinchTheDatabase. recompilation time isProportional toThe DOC>    Number  ofInvalid objectsinchTheDatabase, so the command may take DOC>ALongTime to Execute  onADatabase  withA large Number  ofInvalid DOC>objects. DOC>DOC>    UseThe following queries toTrack recompilation Progress:doc>DOC>   1. Query returning the Number  ofInvalid objects remaining. This DOC>       NumberShould decrease withTime . DOC>         SELECT COUNT(*) fromobj$WHEREStatusinch(4,5,6); DOC>DOC>   2. Query returning the Number  ofObjects compiled so far. This NumberDOC>should increase withTime . DOC>         SELECT COUNT(*) fromutl_recomp_compiled; DOC>DOC>This script automatically chooses serialorparallel recompilation DOC>Based onThe Number  ofCPUs available (parameter cpu_count) multiplied DOC>    byThe Number  ofthreads per CPU (parameter parallel_threads_per_cpu). DOC>    onRAC, this Number  isAdded across AllRAC nodes. DOC>DOC>Utl_recomp uses Dbms_scheduler to CreateJobs forParallel DOC>recompilation. Jobs is created without instance affinity so they DOC>Can migrate across RAC nodes. UseThe following queries toVerify DOC>Whether Utl_recomp jobs is being created andRun Correctly:doc>DOC>   1. Query showing jobs created byUtl_recomp DOC>         SELECTJob_name fromdba_scheduler_jobs DOC>            WHEREJob_name like 'utl_recomp_slave_%'; DOC>DOC>   2. Query showing Utl_recomp jobs that is running DOC>         SELECTJob_name fromdba_scheduler_running_jobs DOC>            WHEREJob_name like 'utl_recomp_slave_%'; DOC># PL/The SQL process has completed successfully. TIMESTAMP
--------------------------------------------------------------------------------Comp_timestamp Utlrp_end .- ,- -  -:Geneva: -PL/The SQL process has completed successfully. DOC>The following query reports the Number  ofobjects that has compiled DOC>  withErrors (objects that compile withErrors has StatusSet  to 3 inchDOC>obj$).IfThe Number  ishigher than expected, please examine the error DOC>Messages reported withEach object (using SHOW ERRORS) toSeeifthey DOC>Point toSystem misconfigurationorresource constraints that must be DOC>Fixed before attempting torecompile these objects. DOC># OBJECTS withERRORS-------------------
                  4DOC>The following query reports the Number  oferrors caught during DOC>recompilation.IfThis Number  isNon-Zero, please query the error DOC>MessagesinchTheTableUtl_recomp_errors toSeeif  any  ofThese errors DOC>is due toMisconfigurationorresource constraints that must be DOC>fixed before objects can compile successfully. DOC># ERRORS DURING recompilation---------------------------
                          0PL/The SQL process has completed successfully.

Further study of the file SQL file, you can see that, by default, Oracle calls the stored procedure utl_recomp.recomp_parallel to compile the invalid package in parallel:

begin 
  sys. Utl_recomp.recomp_parallel (0);
 End;

When the threads value is 0 o'clock, the parallelism is determined by Oracle according to the parameters Cpu_count and parallel_threads_per_cpu;

SQL>  show parameter CPU

NAME                                 TYPE        VALUE
----------------------- --------------------------------------
cpu_count                            integer      4 
PARALLEL_THREADS_PER_CPU             integer     2

Sometimes, because of Oracle bug 14065287, when you enable parallel compilation of invalid objects, the script utlrp.sql the hang phenomenon, you need to enable the serial compilation of invalid objects, as follows:

BEGIN 
   sys.utl_recomp.recomp_serial ();
 END;

Note: If it is interrupted during execution, the next time it is executed again, it is possible that the name is already in use by an existing pair, and the following indexes should be removed before recompiling

Drop Index SYS. utl_recomp_comp_idx1;

When using parallel execution, the large pool in the SGA is used, and if the large pool size is not large enough, the following error is reported:

ORA-12801: Error signal emitted in parallel query server P012 ORA-12853: Not enough memory for PX buffer: current 16336K, Max 178560K ORA required-04031: Unable to allocate65560bytes of shared memory ("Large pool", "Unknown Object", "Large Pool", "PX msg Pool") ORA-06512: In "SYS. Utl_recomp ", line865ORA-06512: On line2

If it is not a sys user execution, you can grant the relevant execution permission first:

Grant Execute  on  to XXX;

Oracle recompile failed pair image