概述

Oracle数据库有时候会发现存在一些失效对象，特别是做迁移之类，如果失效对象比较多一个一个编译也是很麻烦，那么有没脚本可以把相关的失效对象一次性编译呢？

1、compile_all_bodies.sql

Description : Compiles all invalid package bodies for specified schema, or all schema.

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
​
SPOOL temp.sql
​
SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE BODY;'
FROM all_objects a
WHERE a.object_type = 'PACKAGE BODY'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));
​
SPOOL OFF
​
-- Comment out following line to prevent immediate run
@temp.sql
​
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

2、compile_all_funcs.sql

Description : Compiles all invalid functions for specified schema, or all schema.

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
​
SPOOL temp.sql
​
SELECT 'ALTER FUNCTION ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'FUNCTION'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));
​
SPOOL OFF
​
-- Comment out following line to prevent immediate run
@temp.sql
​
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

3、compile_all_procs.sql

Description : Compiles all invalid procedures for specified schema, or all schema

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
​
SPOOL temp.sql
​
SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'PROCEDURE'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));
​
SPOOL OFF
​
-- Comment out following line to prevent immediate run
@temp.sql
​
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

4、compile_all_specs.sql

Description : Compiles all invalid package specifications for specified schema, or all schema.

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
​
SPOOL temp.sql
​
SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'PACKAGE'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));
​
SPOOL OFF
​
-- Comment out following line to prevent immediate run
@temp.sql
​
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

5、compile_all_trigs.sql

Description : Compiles all invalid triggers for specified schema, or all schema.

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
​
SPOOL temp.sql
​
SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'TRIGGER'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));
​
SPOOL OFF
​
-- Comment out following line to prevent immediate run
@temp.sql
​
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

6、compile_all_views.sql

Description : Compiles all invalid views for specified schema, or all schema.

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
​
SPOOL temp.sql
​
SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;'
FROM all_objects a
WHERE a.object_type = 'VIEW'
AND a.status = 'INVALID'
AND a.owner = Decode(Upper('&&1'), 'ALL',a.owner, Upper('&&1'));
​
SPOOL OFF
​
-- Comment out following line to prevent immediate run
@temp.sql
​
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~