Compiling all invalid objects 2

[abhijit74]

There is a package called PL/SQL Developer by AllRound Automation which has a feature of compiling all invalid objects.

How do you do this in SQLPLUS?

Cheers!
Abhijit

 

[SantaMufasa]

Abhijit,

Oracle has a script that re-compiles invalid objects, but I use my own script (below). This script takes care of a single schema; if you want to re-compile all 'INVALID' objects in the database, then you will want to access DBA_OBJECTS instead of USER_OBJECTS and prefix OBJECT_NAME with "...OWNER||'.'||OBJECT_NAME". Be sure to save the following code to a script (such as Recompile.sql) before you run it, otherwise the "SET ECHO OFF" does not take effect.

set pagesize 0
set feedback off
set echo off
spool temp.sql
select 'alter '||object_type||' '||object_name||' compile;'
from user_objects
where status = 'INVALID' and object_type not like '%BODY%'
/
spool off
set feedback on
prompt
prompt Created script, 'temp.sql'. Run that script to recompile Invalid objects.
prompt

Let me know if this is what you wanted.

Dave
Sandy, Utah, USA @ 17:40 GMT, 10:40 Mountain time

 

[sem]

You may also call DBMS_UTILITY.COMPILE_SCHEMA procedure.

Regards, Dima

 

[abhijit74]

Does this DBMS_UTILITY.COMPILE_SCHEMA procedure work in 8.1.7?

 

[karluk]

Yes, dbms_utility.compile_schema has been available for a long time, including in 8.1.7. I use it quite a bit. It doesn't do anything with invalid views, however. If you want to eliminate the "invalid" status on those, you will need to do something similar to the script suggested by SantaMufasa.