RE: Replacing a view with selects on it

From: Justin Cave (DDBC) <jcave_at_ddbcinc.com>
Date: Sun, 16 May 2004 10:09:35 -0600
Message-ID: <87E9F113CEF1D211A4C30090273018742BC9F7@ddbcinc.ddbc.local>

My preference in this sort of environment would be to replace the data in a single transaction, making sure that I had allocated enough UNDO to satisfy any long-running queries. This might mean that the load was a single transaction into a single copy of the tables, it might mean that the load populates a series of staging tables in a number of transactions and there is a single transaction that MERGEs these staging tables into the main tables.

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of jaromir nemec Sent: Sunday, May 16, 2004 9:50 AM
To: oracle-l_at_freelists.org
Subject: Replacing a view with selects on it

H,
=20

There is a common strategy in some DSS systems to refresh the data by replacing the access views (they are repointed to the new version of data). In case there is defined a loading window (with no access) I guess this approach is very safe. But what about a 24*7 environment, if there is no "midnight" to do the replace.

Is it acceptable to simple replace a view if there could be some session reading it?=20

=20

I performed some simple test:

=20

start long select from a view
create or replace the view while the session is running
see what happen:)

The reader session did "survived" (I guess with the cached version of the view).

But it is no prove!=20

Are there situation, where this approach can cause problems? Any experience with this topic on the list?

=20

Thanks

=20

Jaromir D.B. Nemec

http://www.db-nemec.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun May 16 2004 - 11:03:07 CDT