| 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: Replacing a view with selects on it
We use a similar approach too. Instead of views we use synonyms. We have
base1$ and base2$ prefix for the tables. User synonyms will point to the
current base(say base1$) and the load user synonyms will point to the
other base (base2$). After the load completes synonyms are recreated
switching the bases.
So, far we haven't had any issues with this approach. We try to minimize
the switch during online day..
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of DENNIS WILLIAMS
Sent: Monday, May 17, 2004 7:29 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Replacing a view with selects on it
Jaromir
This reminds me of a DW product I've dealt with. All tables are duplicated into a _A set and _B set. Before a data refresh begins, all views point to the _A tables. The data is loaded into the _B set. Then if the load is validated, all views are switched to point to the _B set. If the load fails, the views remain pointing to the _A set, and the users have to wait for fresh data, but are otherwise able to continue processing. I believe there is a final step where the _A tables are truncated and the _B tables are copied into them, but I can't recall exactly. Seemed pretty clever.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of jaromir nemec
Sent: Monday, May 17, 2004 4:52 PM
To: oracle-l_at_freelists.org
Subject: Re: Replacing a view with selects on it
Thanks Daniel, Wolfgang and Justin,
@Daniel - I didn't consider a change of a view *structure*. I meant only a change of the data selected in the view, e.g. a change of the where clause in the view. From this point of view the only *repeatable read* requirement is, that the select don't crash or return wrong result if the view is replaced while performed.
@Wolfgang - good point; the only access of the view while parsing the query - but what happen if the query gets out from library cache and needs to be reparsed?
Could there be a situation that it cause some troubles?
@Justin - yes I admit that the safest way is to perform the change direct with DML. The database guaranties that all is transactional safe. But e.g. in case if you have a massive change in a big snapshot it could be preferable to *prepare* the change in stage area and to *publish* the new version of this snapshot via a replacement of a view (e.g. exchange partition and repointing the access view to the new partition)
regards
Jaromir D.B. Nemec
>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.
>Unless my understanding is utterly wrong, the view definition is only
>required during the parsing of the query.
>My preference in this sort of environment would be to replace the data
>in a single transaction,
-- 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 ----------------------------------------------------------------- -- Attached file included as plaintext by Ecartis -- -- Desc: Signature The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ---------------------------------------------------------------- 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 Mon May 17 2004 - 20:54:39 CDT
 |
 |