| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_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_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
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-LReceived on Mon Nov 20 2000 - 15:46:22 CST
(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). ------_=_NextPart_001_01C0533B.5351DE20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: Views and triggers invalidated after creating and/or = dropping</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>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.</FONT></P> <P><FONT SIZE=3D2>Alex Hillman</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Mohammad Rafiq [<A = HREF=3D"mailto:rafiq9857_at_hotmail.com">mailto:rafiq9857_at_hotmail.com</A>]<= /FONT> <BR><FONT SIZE=3D2>Sent: Saturday, November 18, 2000 1:41 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: RE: Views and triggers invalidated after = creating and/or</FONT> <BR><FONT SIZE=3D2>dropping</FONT> </P> <BR> <P><FONT SIZE=3D2>Alex,</FONT> <BR><FONT SIZE=3D2>With highly intregted databases like Oracle = Financials there are lot of </FONT> <BR><FONT SIZE=3D2>dpendencies exist between objects and even if you = drop indexes for AR/AP </FONT> <BR><FONT SIZE=3D2>tables for purpose of reorg you will find lot of = views will become invalid.</FONT> <BR><FONT SIZE=3D2>To check this aspect Enterprise Manager is a good = tool to check for </FONT> <BR><FONT SIZE=3D2>dependencies.</FONT> <BR><FONT SIZE=3D2>Regards</FONT> <BR><FONT SIZE=3D2>Rafiq</FONT> </P> <BR> <P><FONT SIZE=3D2>Reply-To: ORACLE-L_at_fatcity.com</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L = <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=3D2>Date: Fri, 17 Nov 2000 13:10:31 -0800</FONT> </P> <P><FONT SIZE=3D2>What dependency has view on indexes?</FONT> </P> <P><FONT SIZE=3D2>Alex Hillman</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>Sent: Friday, November 17, 2000 3:01 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>dropping ind</FONT> </P> <BR> <P><FONT SIZE=3D2>It is normal behaviour due to dependencies and it is = applicable to all</FONT> <BR><FONT SIZE=3D2>oracle versions including 7.3.4.</FONT> <BR><FONT SIZE=3D2>Just recompile those invalids on fly.</FONT> <BR><FONT SIZE=3D2>Regards</FONT> <BR><FONT SIZE=3D2>Rafiq</FONT> </P> <BR> <P><FONT SIZE=3D2>Reply-To: ORACLE-L_at_fatcity.com</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L = <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=3D2>Date: Fri, 17 Nov 2000 09:55:23 -0800</FONT> </P> <P><FONT SIZE=3D2>Oracle 8.1.6.2, Sun Solaris 7.</FONT> </P> <P><FONT SIZE=3D2>Anybody have any idea why dropping indexes can cause = invalidation of views?</FONT> </P> <P><FONT SIZE=3D2>Alex Hillman</FONT> </P> <P><FONT = SIZE=3D2>_______________________________________________________________= __________</FONT> <BR><FONT SIZE=3D2>Get Your Private, Free E-mail from MSN Hotmail at <A = HREF=3D"http://www.hotmail.com" = TARGET=3D"_blank">http://www.hotmail.com</A>.</FONT> </P> <P><FONT SIZE=3D2>Share information about yourself, create your own = public profile at</FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://profiles.msn.com" = TARGET=3D"_blank">http://profiles.msn.com</A>.</FONT> </P> <P><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Author: Mohammad Rafiq</FONT> <BR><FONT SIZE=3D2> INET: rafiq9857_at_hotmail.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed = from). You may</FONT> <BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P> <P><FONT = SIZE=3D2>_______________________________________________________________= __________</FONT> <BR><FONT SIZE=3D2>Get Your Private, Free E-mail from MSN Hotmail at <A = HREF=3D"http://www.hotmail.com" = TARGET=3D"_blank">http://www.hotmail.com</A>.</FONT> </P> <P><FONT SIZE=3D2>Share information about yourself, create your own = public profile at </FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://profiles.msn.com" = TARGET=3D"_blank">http://profiles.msn.com</A>.</FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Mohammad Rafiq</FONT> <BR><FONT SIZE=3D2> INET: rafiq9857_at_hotmail.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
 |
 |