Home > DBA > How to identify and recompile INVALID OBJECTS?

How to identify and recompile INVALID OBJECTS?

When I need to identify invalidated objects I run as sys the following query to list them:

 

SQL> select *

2  from all_objects

3  where status = ‘INVALID’ ;

 

Next is recompiling

SQL> exec UTL_RECOMP.RECOMP_SERIAL;

 

Another way is to  generate with a script:

sqlplus /nolog

conn sys/psw@SID as sysdba;

set heading off;

set feedback off;

set echo off;

set lines 900;

spool run_recomp_invalid.sql

select ‘ALTER ‘ || OBJECT_TYPE || ‘ ‘ || OWNER || ‘.’ || OBJECT_NAME || ‘ COMPILE;’

from dba_objects

where status = ‘INVALID’

and object_type in (‘PACKAGE’,’FUNCTION’,’PROCEDURE’);

spool off;

set heading on;

set feedback on;

set echo on;

 

Cheers,

Maria

Advertisements
Categories: DBA
  1. June 17, 2013 at 2:38 pm

    Thank you! You saved me MUCH time. I will use your script often.

  1. No trackbacks yet.

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: