Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 23 August 2012

Script: Recompile Invalid Objects

SELECT DISTINCT 'alter session set current_schema=' ||  owner || ';' cmd, owner, 1 order_col, NULL object_name
FROM dba_objects
WHERE status = 'INVALID' AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE',  'FUNCTION', 'TRIGGER')
UNION
SELECT 'ALTER ' ||  DECODE ( object_type, 'PACKAGE BODY', 'PACKAGE', object_type  ) || ' ' ||  owner ||  '.' ||  object_name || ' COMPILE' ||
DECODE ( object_type, 'PACKAGE BODY', ' BODY',  ''  ) || ';' cmd, owner, 2 order_col, object_name
FROM dba_objects outer
WHERE status = 'INVALID'  AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
AND  ( object_type <> 'PACKAGE BODY' OR NOT EXISTS ( SELECT NULL
FROM dba_objects
WHERE owner =  outer.owner AND object_name = outer.object_name
   AND object_type = 'PACKAGE' AND status =  'INVALID')
  )
 ORDER BY 2, 3, 4
/

0 comments:

Post a Comment