Periodically, I observe that some of the stored procedures become invalid. There have been no changes to the tables, or to the stored procedure. Why does this happen ? Is there a way to see "why" a stored procedure is invalid ?
Appreciate your help
Typically, if a dependant object has been altered, it will invalidate the stored procedure. This includes stored procedures, packages, tables, types, etc.
In Oracle 7 (And maybe in 8) a procedure could go "INVALID" if it wasn't accessed for a while. I'm not sure if this is still true.
Is there a way to see the reason why the stored procedure got invalid (something like show errors).
Try looking at DBA_ERRORS
A stored procedure becomes invalid typically when a referenced object is altered or becomes invalid. Here is a script to find out how procedures are invalid and to recompile them automatically.
set heading off;
set feedback on;
set pages 10000;
prompt ****** NOTE: Append a .sql when entering spoolfile name
select 'alter '||object_type, object_name
where status = 'INVALID'
and object_type != 'PACKAGE BODY'
select 'alter package '||NULL, object_name
||' compile body;'
where status = 'INVALID'
and object_type = 'PACKAGE BODY';
host rm /temp/&&spoolfile; del \temp\&&spoolfile;
you dont need any script, just run $ORACLE_HOME/rdbms/admin/utlrp.sql, it compiles all invalid objects (views, procs, packages, functions, triggers etc)
When I looked into the utilrp.sql script, it does the following
1. invalidate all pl/sql modules and recompile standard and dbms_standard
2. recompile all invalid pl/sql modules
and they say you have to connect as internal to do it.
Does this script invalidate and recompile "ALL" the pl/sql modules in the database ? If so, isnt it an overhead ?
Well, Pando sugested UTLRP.SQL, not UTLIRP.SQL.
UTLRP.SQL only recompiles object which are invalid. So no overhead here.
UTLIRP.SQL in turn first calls UTLIP.SQL which invalidates *all* object in the database, then it calls UTLRP.SQL which will recompile *all* database objects, which has just be invalidated by previous script. So yes, there is an overhead with this combination, but it has its purpose: it is ment to use after database migration where all objects should be recompiled.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Thanks so much for clarifying, I would NEVER have guessed there were two DIFFERENT scripts utlrp.sql and utilrp.sql !!
I will use the utlrp.sql script to validate all invalid objects.
Thanks Pando !
Click Here to Expand Forum to Full Width