Invalid object script’s


SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

select
   'ALTER ' || OBJECT_TYPE || ' ' ||
   OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
   dba_objects
where
   status = 'INVALID'
--and
--  object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;

SELECT owner, object_type, object_name, status
          FROM sys.dba_objects
         WHERE     (owner LIKE 'OC_%' OR owner LIKE 'PR_%')
               AND object_type NOT IN ('TABLE', 'INDEX', 'SEQUENCE', 'SYNONYM', 'LOB', 'DATABASE LINK')
               AND status = 'INVALID'
      ORDER BY 1, 2, 3;

select 'ALTER TRIGGER ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
   from dba_objects
 where status = 'INVALID'
    AND OBJECT_TYPE = 'TRIGGER'
 union
 select 'ALTER FUNCTION ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
   from dba_objects
 where status = 'INVALID'
    AND OBJECT_TYPE = 'FUNCTION'
 union
 select 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;'
   from dba_objects
 where status = 'INVALID'
    AND OBJECT_TYPE = 'PACKAGE BODY'
 union
 select 'ALTER PROCEDURE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
   from dba_objects
 where status = 'INVALID'
    AND OBJECT_TYPE = 'PROCEDURE'
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: