How to compile all procedures in a database. what are the priviledges do we need for that.
thanks
Printable View
How to compile all procedures in a database. what are the priviledges do we need for that.
thanks
Hi,
The following sql will be able to list out the command to compile all the invalid procedures in your database. You will need to have alter procedure privileges.
Hopes that this helps :)
select decode( OBJECT_TYPE, 'PROCEDURE',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from dba_objects
where STATUS = 'INVALID'
and OBJECT_TYPE = 'PROCEDURE'
and owner = 'ORCL'
order by OWNER, OBJECT_TYPE, OBJECT_NAME;
Hopes also this will do
select 'Alter Procedure ' || OWNER||'.'||OBJECT_NAME || ' compile;'
from dba_objects
where STATUS = 'INVALID'
and OBJECT_TYPE = 'PROCEDURE'
and owner = 'ORCL'
order by OWNER, OBJECT_NAME;
Thanks
P Soni
Hey,
What about DBMS_UTILITY.Compile_Schema(<>) .... ????
Ask your DBA to run utlrcmp.sql or utlrp.sql from $ORACLE_HOME/rdbms/admin, which would recompile all the invalid procedures ....
Sam