Re: What happens to transactions accessing a view during a REPLACE VIEW?

On 12 Apr 2002 10:43:44 -0700, jimlorraine_at_hotmail.com (jim rowe) wrote:

>Hi,
>
>Suppose i have a simple view pointing to a simple table.
>
>CREATE VIEW my_view AS (select column_a from table_a);
>
>A high performance application is accessing this view thousands of
>times a second. It must run 24 * 7. They are read-only accesses.
>
>I want to replace the view with the following command.
>
>CREATE OR REPLACE VIEW my_view AS (select column_b from table_b).
>
>When i run this command, does the view suffer any 'downtime' during
>the changeover? Will the application be safely blocked from accessing
>the view during the transition, or will some of its accesses to the
>view fail?
>
>What about transactions in progress on the view during the transition.
>Will they be allowed to complete, or will they fail?
>
>Thanks for your help,
>Jim.

If the view is in use you won't be able to replace it, as the view has a lock in the datadictionary and your create or replace will need to acquire an exclusive lock.

In addition I don't see any advantage to replace the underlying table by a view, as a view is a virtual object and still queries the table.

--
Regards


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address