Re: Replacing a view with selects on it
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 17 May 2004 08:34:22 -0600
There is one problem with this that I can see. By changing the view while
another session has referenced it, you invite the
possibility of a non-repeatable read.
In the example, I have 2 sessions running. The commands are mapped to each
session by the S1 or S2 prefix and the commands are
displayed in chronological order
ex1: Simple recreation of view
S1: create view seq_view as (select rec_no, insert_text from sequential_data);
S1: select * from seq_view;
S1: output from running query
REC_NO INSERT_TEXT
----------
--------------------------------------------------------------------------------
1 Record 1
2 Record 2
S2: create or replace view seq_view as (select insert_date, rowid_blocknum,
rowid_rownum from sequential_data);
S1: ouptut continues
REC_NO INSERT_TEXT
----------
--------------------------------------------------------------------------------
999997 Record 999997
999999 Record 999999
500000 rows selected.
S1: / -- to invoke the command still in the buffer
S1: output from running query
INSERT_DA ROWID_BLOCKNUM ROWID_ROWNUM
--------- -------------- ------------
22-MAR-04 109118 7
22-MAR-04 109118 9
22-MAR-04 109118 11
22-MAR-04 109118 13
22-MAR-04 109118 15
22-MAR-04 109118 17
500000 rows selected.
-- The reexecution of the statement was performed against the new view. The old
view was recreated, so the old view definition was
invalidated in the library cache and a hard parse was required. This could
cause a non-repeatable read in an application or query.
One area to check is what happens if the view is referenced as a cursor inside
a pl/sql loop. The cursor is opened against view1 and
data is retrieved. Then the cursor is closed, but the pl/sql block is still
executing. The view is changed. The cursor is opened
again, but this time the view referenced contains different data. Definitely
something to test.
Daniel Fink
Wolfgang Breitling wrote:
> At 09:49 AM 5/16/2004, you wrote:
>
>
>>1) start long select from a view
>>
>>2) create or replace the view while the session is running
>>3) see what happen:)
>>
>> The reader session did "survived" (I guess with the cached version of
>>the view).
>
>
> Unless my understanding is utterly wrong, the view definition is only
> required during the parsing of the query. Once it starts the execute/fetch
> phase the plan is bound to the physical objects and the view definition can
> change.
> I am confident that Oracle would prevent view changes until the query
> finishes if it was needed beyond the parse.
>
>
>
>>But it is no prove!
>>
>>Are there situation, where this approach can cause problems? Any
>>experience with this topic on the list?
>
>
> Based on my understanding and assessment, I do not foresee any problems. I
> have not done it on a massive or regular basis, but I have never had a
> problem altering a view definition in a live system, except when not using
> replace and permissions get lost temporarily.
>
>
>
>>Jaromir D.B. Nemec
>
>
> regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
