重新编译失效对像可执行utlrp.sql文件：
SQL> @?/rdbms/admin/utlrp.sql TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2016-08-24 13:04:49 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL 过程已成功完成。 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2016-08-24 13:04:50 PL/SQL 过程已成功完成。 DOC> The following query reports the number of objects that have compiled DOC> with errors (objects that compile with errors have status set to 3 in DOC> obj$). If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 4 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 PL/SQL 过程已成功完成。
进一步研究文件sql文件，可以看到，在默认情况下Oracle会调用存储过程utl_recomp.recomp_parallel并行编译无效包：
begin sys.UTL_RECOMP.recomp_parallel(0); end;
当threads取值为0时，由Oracle根据参数cpu_count和parallel_threads_per_cpu自行决定并行度;
SQL> show parameter cpu NAME TYPE VALUE ------------------------------------ ----------- -------------- cpu_count integer 4 parallel_threads_per_cpu integer 2
有时候，由于Oracle bug 14065287，在启用并行编译无效对象时，脚本utlrp.sql会出现HANG现象，这时需要启用串行编译无效对象，如下所示：
BEGIN sys.utl_recomp.recomp_serial(); END;
注意：如果在执行中，中断了，下次如果再次执行时，有可能会出现名称已由现有对像使用,要在重编译前先删除下列索引
drop index SYS.UTL_RECOMP_COMP_IDX1;
使用并行执行时，会使用到SGA中的large pool,如果large pool大小不够大，会报如下错误：
ORA-12801: 并行查询服务器 P012 中发出错误信号 ORA-12853: PX 缓冲区的内存不足: 当前为 16336K, 最大需要 178560K ORA-04031: 无法分配 65560 字节的共享内存 ("large pool","unknown object","large pool","PX msg pool") ORA-06512: 在 "SYS.UTL_RECOMP", line 865 ORA-06512: 在 line 2
如果不是sys用户执行，可先授予相关的执行权限：
grant execute on UTL_RECOMP to XXX;
