重新编译失效对像可执行utlrp.sql文件:
SQL> @?/rdbms/admin/utlrp.sqlTIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-08-24 13:04:49DOC> The following PL/SQL block invokes UTL_RECOMP torecompile invalid
DOC> objects in the database. Recompilation time is proportional tothe
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 ofinvalid
DOC>objects.
DOC>DOC> Use the following queries totrack recompilation progress:
DOC>DOC> 1. Query returning the number ofinvalid objects remaining. This
DOC> number should decrease withtime.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>DOC> 2. Query returning the number of objects compiled so far. This numberDOC> should increase withtime.
DOC> SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;
DOC>DOC> This script automatically chooses serial orparallel recompilation
DOC> based on the number ofCPUs available (parameter cpu_count) multiplied
DOC> by the number ofthreads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across allRAC nodes.
DOC>DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs forparallel
DOC>recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries toverify
DOC> whether UTL_RECOMP jobs are being created andrun correctly:
DOC>DOC> 1. Query showing jobs created byUTL_RECOMP
DOC> SELECT job_name FROMdba_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 FROMdba_scheduler_running_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%‘;
DOC>#
PL/SQL 过程已成功完成。TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-08-24 13:04:50PL/SQL 过程已成功完成。
DOC> The following query reports the number ofobjects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 inDOC> obj$). If the number ishigher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see ifthey
DOC> point to system misconfiguration orresource constraints that must be
DOC> fixed before attempting torecompile these objects.
DOC>#
OBJECTSWITHERRORS-------------------
4DOC> The following query reports the number oferrors 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 ofthese errors
DOC> are due to misconfiguration orresource constraints that must be
DOC>fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION---------------------------
0PL/SQL 过程已成功完成。
进一步研究文件sql文件,可以看到,在默认情况下Oracle会调用存储过程utl_recomp.recomp_parallel并行编译无效包:
beginsys.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 4parallel_threads_per_cpuinteger 2
有时候,由于Oracle bug 14065287,在启用并行编译无效对象时,脚本utlrp.sql会出现HANG现象,这时需要启用串行编译无效对象,如下所示:
BEGINsys.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 865ORA-06512: 在 line 2
如果不是sys用户执行,可先授予相关的执行权限:
grant execute on UTL_RECOMP to XXX;
原文:http://www.cnblogs.com/willspring/p/5802552.html