需求

**表变更导致数据库对象失效问题处理**

导致数据库对象失效的原因：

An INVALID Object problem may arise due to:

    Due to any patch installation.
    Database/Application level up-gradation
    DDL Changes

1. 查看需要变更的表所关联的存储过程、视图等对象及其状态

SELECT owner, object_type, object_name, status,created,last_ddl_time
     FROM all_objects
    WHERE 
       object_name in (SELECT name FROM dba_dependencies WHERE OWNER = 'table_owner' AND referenced_name='table_name' )
       --AND status = 'INVALID'
     ORDER BY OWNER; 

2. 查看对象的ddl脚本

SELECT *
    FROM dba_source
   WHERE NAME = 'object_name'
         and OWNER = 'owner'
ORDER BY line;

3. 变更表所相关的对象，会在变更过程中可能会失效。与开发人员确认是否需要变更窗口。

4.变更后查看无效对象：

SELECT CASE WHEN object_type = 'SYNONYM' AND owner = 'PUBLIC' THEN
    'alter ' || owner || ' ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    ELSE
    'alter ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || owner || '.' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    END "SQL_COMMANDS"
    FROM dba_objects
    WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SYNONYM')
    AND status = 'INVALID'
    AND OWNER='owner'    
    ORDER BY DECODE(object_type, 'TRIGGER', '99', '00');
    
或
  EXEC DBMS_UTILITY.compile_schema('owner');    

How to Recompile Invalid Database Objects (Doc ID 1272098.1)