Oracle-L: RE: Recompiling Invalid Objects after Table Rename

From: Nicoll, Iain (Calanais) <iain.nicoll_at_calanais.com>
Date: Mon, 15 Apr 2002 05:28:23 -0800
Message-ID: <F001.004444EB.20020415052823@fatcity.com>

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: wbuchan_at_uk.intasys.com

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: ListGuru_at_fatcity.com (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: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------