Can't you just do a variant of
SET HEAD OFF TERMOUT OFF ECHO OFF
select 'alter '||decode(object_type,'PACKAGE BODY',' PACKAGE
',object_type)||
' '||object_name||' compile '||
decode(object_type,'PACKAGE BODY','BODY ','')||';'
from user_objects
where object_type in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION',
'VIEW','TRIGGER')
and status != 'VALID'
spool recomp.sql
/
spool off
SET HEAD ON TERMOUT ON ECHO ON
start recomp
which will attempt to recompile anything invalid.
Option 1 wouldn't necessarily ever work as I believe recompilation only
happens when something is called directly i.e. if the user calls a package
which in turn calls an invalid package then recompilation would not happen
(apologies if I'm wrong)
Iain Nicoll
-----Original Message-----
Sent: Monday, April 15, 2002 1:23 PM
To: Multiple recipients of list ORACLE-L
Hi all,
We have an application which deletes a large number of rows from a
table. It would be faster to simply insert the rows that we want to keep
into a second table, drop the original table and then rename the second
table to that of the one we have just dropped.
The only downside that I can see is that all the source objects which
reference the original table become invalid.
We could:
1. Simply allow the source objects to be recompiled naturally overtime
as they are reused (but with the possibility of a large number of invalid
objects at any one time in the database and little control over when
compilation is done).
2. Force recompilation following the drop table. However this would
require logging all objects which would need recompilation. This is an
additional step for any new development and would therefore the list of
object would be prone to become inaccurate over time. (Could maybe do this
automatically using USER_REFERENCES prior to the drop table? - still seems
a bit clumsy)
Does anyone have any comments on doing this?
Many thanks
- Bill.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Buchan
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
