Re: Rename table transaction

You will need to revoke access during this time, and regrant it when finished.

You can revoke access to and rename a table while another session is querying it.

In the case of revoking access, it will not take effect until the other session ends.

In the case of the table rename, the other session will be unable to access the table using the old name as soon as the query finishes.

This is all getting rather complex, are you sure this process is necessary?

Jared

On Fri, 18 Mar 2005 12:59:32 +0100, Denys van Kempen <dvkempen_at_gmail.com> wrote:
> Jared,
>
> Thanks for you reply. However one issues remains. We need to guarantee
> that nobody can attempt to access any of the 2 tables while I'm
> switching
>
> What we want to do is
> 1 Lock table A, table B
> 2 Rename A to C
> 3 Rename B to A
> 4 Rename C to B
> 5 Release table A, table B
>
> If I can't lock during processing, another user might attempt and fail
> a select on A after step 2. (or B after step 3).
>
> I tried locking the table but the rename went ahead regardless. Any ideas?
>
> Thanks for you time.
>
> Denys
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l