Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Views and triggers invalidated after creating and/or dropping

RE: Views and triggers invalidated after creating and/or dropping

From: The Oracle DBA <theoracledba_at_lycos.com>
Date: Mon, 20 Nov 2000 17:15:20 -0500
Message-Id: <10686.122554@fatcity.com>


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).
Received on Mon Nov 20 2000 - 16:15:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US