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

Message-Id: <10686.122551@fatcity.com> From: Alex Hillman Date: Mon, 20 Nov 2000 16:46:22 -0500 Subject: RE: Views and triggers invalidated after creating and/or dropping This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0533B.5351DE20 Content-Type: text/plain; charset="iso-8859-1" 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----- From: Mohammad Rafiq [mailto:rafiq9857@hotmail.com] Sent: Saturday, November 18, 2000 1:41 PM To: Multiple recipients of list ORACLE-L Subject: RE: Views and triggers invalidated after creating and/or 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@fatcity.com To: Multiple recipients of list ORACLE-L 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@fatcity.com To: Multiple recipients of list ORACLE-L 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@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@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@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@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). ------_=_NextPart_001_01C0533B.5351DE20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Views and triggers invalidated after creating and/or = dropping

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-----
From: Mohammad Rafiq [mailto:rafiq9857@hotmail.com]<= /FONT>
Sent: Saturday, November 18, 2000 1:41 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Views and triggers invalidated after = creating and/or
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@fatcity.com
To: Multiple recipients of list ORACLE-L = <ORACLE-L@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@fatcity.com
To: Multiple recipients of list ORACLE-L = <ORACLE-L@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@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@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@hotmail.com