Re: Recompiling Invalid Objects after Table Rename

Unfortunately we do not have the partitioning option available. I should mention that when I say I want to delete a large number of rows, the number of rows I want to keep is even larger. (Delete, say, 3 million rows, keep 7 million rows) so the option that some other have suggested of truncating the original table and copying the rows back would likely be much slower than recompiling the invalid objects. Thanks to all those who have suggested a way of doing so.

• Bill.

At 04:58 15/04/02 -0800, you wrote:
>$ORACLE_HOME/rdbms/admin/utlrp will recompile all invalid objects.
>
>Could you partition the table so that you only need to drop a partition
>instead of deleting rows?
>
>Jay
>
> >>> wbuchan_at_uk.intasys.com 04/15/02 08:23AM >>>
>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: Jay Hostetter
> INET: jhostetter_at_decommunications.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: 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).