Oracle 编译无效对象
DBA命令速查4:Oracle 编译无效对象(Compile Invalid Objects)
发布于 2022-08-22 13:26:23
编者按:留存一下供自己需要时查找。
【免责声明】本号文章仅代表个人观点,与任何公司无关,仅供参考。
编辑|SQL和数据库技术(ID:SQLplusDB)
查看无效对象
例1:
SQL> SET LINES 333 PAGES 111
SQL> COL OBJECT_NAME FOR A40
SQL> COL OBJECT_TYPE FOR A30
SQL> COL OWNER FOR A25
SQL> SELECT OWNER, OBJECT_TYPE,COUNT(*) OBJECT_COUNT FROM DBA_OBJECTS WHERE STATUS <> 'VALID'
2 GROUP BY OWNER,OBJECT_TYPE order by OWNER,OBJECT_TYPE;
OWNER OBJECT_TYPE OBJECT_COUNT
------------------------- ------------------------------ ------------
TEST PROCEDURE 1
TESTC100 FUNCTION 2
例2:
SQL> SET LINES 333 PAGES 111
SQL> COL OBJECT_NAME FOR A40
SQL> COL OBJECT_TYPE FOR A30
SQL> SELECT OWNER, OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS
2 WHERE STATUS <> 'VALID' ORDER BY OWNER, OBJECT_NAME;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------- ---------------------------------------- ------------------------------
TEST DO_CURSORS PROCEDURE
TESTC100 DEBUG_VERSION_RPT FUNCTION
TESTC100 VERSION_RPT FUNCTION
查看某对象的依赖关系对象:
SELECT * FROM DBA_DEPENDENCIES WHERE NAME='&OBJECTNAME';
编译个别对象:
例:
--编译程序包
ALTER PACKAGE package_name COMPILE;
---分别编译程序包PACKAGE和BODY
ALTER PACKAGE package_name COMPILE PACKAGE;
ALTER PACKAGE package_name COMPILE BODY;
--编译视图和触发器
ALTER VIEW <SCHEMA NAME>.<VIEW_NAME> COMPILE;
ALTER TRIGGER <SCHEMA).<TRIGGER_NAME> COMPILE;
--查看是否有编译错误
show errors
PL/SQL生成脚本编译所有无效用户对象
例1:
--编译所有无效PROCEDURE对象
BEGIN
FOR cur IN (
SELECT
object_name
,object_type
FROM dba_objects
WHERE object_type = 'PROCEDURE'
AND status = 'INVALID'
)
LOOP
DBMS_OUTPUT.PUT_LINE(cur.object_name);
EXECUTE IMMEDIATE 'ALTER ' || cur.object_type || ' ' || cur.object_name || ' COMPILE';
END LOOP;
END;
/
例2:
--生成手动编译无效对象的脚本,然后执行脚本。
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS WHERE STATUS = 'INVALID' AND
OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW','SYNONYM','JAVA SOURCE','JAVA CLASS');
utlrp.sql and utlprp.sql脚本编译数据库失效对象
Oracle提供了utlrp.sql和utlprp.sql脚本来重新编译数据库中的所有无效对象,
例:
@$ORACLE_HOME/rdbms/admin/utlrp.sql
@$ORACLE_HOME/rdbms/admin/utlprp.sql
DBMS_UTILITY.compile_schema编译数据库失效对象
DBMS_UTILITY包中的COMPILE_SCHEMA过程编译指定模式中的所有过程,函数,包和触发器。
例:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);
UTL_RECOMP包编译数据库失效对象
UTL_RECOMP包中包含两个用于重新编译无效对象的过程。
RECOMP_SERIAL过程一次重新编译所有无效对象,而RECOMP_PARALLEL过程使用指定数量的线程并行执行相同的任务。

例:
-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');
-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);
-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
参考:
PL/SQL Packages and Types Reference
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_RECOMP.html
>275 UTL_RECOMP
其他参考
https://oracle-base.com/articles/misc/recompiling-invalid-schema-objects
Recompiling Invalid Schema Objects
Oracle オブジェクトを一括で再コンパイルする方法
https://www.saka-en.com/oracle/oracle-recompile-bulk/
本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2021-10-12,如有侵权请联系 cloudcommunity@tencent.com 删除