It sounds like a cool trick. The DBA seeing all the hits against the
data dictionary as the entire select is rerun might not think it's all that cool.
----- Original Message -----
From: The Oracle DBA <theoracledba_at_lycos.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: Monday, November 20, 2000 4:18 PM
Subject: RE: Views and triggers invalidated after creating and/or dropping
> I saw a good trick
>
> create a proc with a cursor of invalid objects
> get the first one
> compile it
> close the cursor
> open the cursor
> now you have only what is STILL invalid
> go until "done"
>
> Cheers,
>
> Earl
>
>
>
>
> On Mon, 20 Nov 2000 13:56:02
> Alex Hillman wrote:
> >I understood now what happened. View is a select statement. When view is
> >created or compiled optimizer creates execution plan. If this execution plan
> >use index which was dropped, this execution plan will be invalidated and so
> >is the view. The same reasoning can be apply to triggers, stored procedures
> >and package bodies which contain SQL statements.
> >
> >Alex Hillman
> >
> >-----Original Message-----
> >Sent: Saturday, November 18, 2000 1:41 PM
> >To: Multiple recipients of list ORACLE-L
> >dropping
> >
> >
> >Alex,
> >With highly intregted databases like Oracle Financials there are lot of
> >dpendencies exist between objects and even if you drop indexes for AR/AP
> >tables for purpose of reorg you will find lot of views will become invalid.
> >To check this aspect Enterprise Manager is a good tool to check for
> >dependencies.
> >Regards
> >Rafiq
> >
> >
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Date: Fri, 17 Nov 2000 13:10:31 -0800
> >
> >What dependency has view on indexes?
> >
> >Alex Hillman
> >
> >-----Original Message-----
> >Sent: Friday, November 17, 2000 3:01 PM
> >To: Multiple recipients of list ORACLE-L
> >dropping ind
> >
> >
> >It is normal behaviour due to dependencies and it is applicable to all
> >oracle versions including 7.3.4.
> >Just recompile those invalids on fly.
> >Regards
> >Rafiq
> >
> >
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Date: Fri, 17 Nov 2000 09:55:23 -0800
> >
> >Oracle 8.1.6.2, Sun Solaris 7.
> >
> >Anybody have any idea why dropping indexes can cause invalidation of views?
> >
> >Alex Hillman
> >
> >_________________________________________________________________________
> >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
> >
> >Share information about yourself, create your own public profile at
> >http://profiles.msn.com.
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Mohammad Rafiq
> > INET: rafiq9857_at_hotmail.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).
> >
> >_________________________________________________________________________
> >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
> >
> >Share information about yourself, create your own public profile at
> >http://profiles.msn.com.
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Mohammad Rafiq
> > INET: rafiq9857_at_hotmail.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).
> >
>
>
> Get FREE Email/Voicemail with 15MB at Lycos Communications at http://comm.lycos.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: The Oracle DBA
> INET: theoracledba_at_lycos.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
Received on Mon Nov 20 2000 - 17:51:52 CST
