-
Hi,
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.
Jeff Hunter
-
Is there a way to see the reason why the stored procedure got invalid (something like show errors).
-
Hi,
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
spool /temp/&&spoolfile
select 'alter '||object_type, object_name
||' compile;'
from user_objects
where status = 'INVALID'
and object_type != 'PACKAGE BODY'
union
select 'alter package '||NULL, object_name
||' compile body;'
from user_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY';
spool off
@/temp/&&spoolfile;
host rm /temp/&&spoolfile; del \temp\&&spoolfile;
undefine 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.
Jurij Modic
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 !
Neelima
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|