oracle重编译utl,oracle重新编译失效对像

重新编译失效对像可执行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