动态数据库失效_数据库索引失效

概述

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

1、compile_all_bodies.sql

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

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF​SPOOL temp.sql​SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE BODY;'FROM all_objects aWHERE 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 14SET FEEDBACK ONSET VERIFY ON

2、compile_all_funcs.sql

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

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF​SPOOL temp.sql​SELECT 'ALTER FUNCTION ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE 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 14SET FEEDBACK ONSET VERIFY ON

3、compile_all_procs.sql

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

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF​SPOOL temp.sql​SELECT 'ALTER PROCEDURE ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE 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 14SET FEEDBACK ONSET VERIFY ON

4、compile_all_specs.sql

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

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF​SPOOL temp.sql​SELECT 'ALTER PACKAGE ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE 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 14SET FEEDBACK ONSET VERIFY ON

5、compile_all_trigs.sql

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

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF​SPOOL temp.sql​SELECT 'ALTER TRIGGER ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE 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 14SET FEEDBACK ONSET VERIFY ON

6、compile_all_views.sql

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

SET PAGESIZE 0SET FEEDBACK OFFSET VERIFY OFF​SPOOL temp.sql​SELECT 'ALTER VIEW ' || a.owner || '.' || a.object_name || ' COMPILE;'FROM all_objects aWHERE 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 14SET FEEDBACK ONSET VERIFY ON

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