Re: enqueue wait while altering tablespace to readonly

"cbarak" <charlinbarak_at_gmail.com> wrote in message news:1125681207.407576.279840_at_f14g2000cwb.googlegroups.com...
>I have a table in tablespace A with uncommitted transaction and when I
> logged in to a different session and tried to alter another tablespace,
> B to read only, the session just hung waiting on enqueue wait. I don't
> understand why the session has to wait on enqueue when the tablespace I
> was trying to alter to read only was different from the tablespace
> containing the uncommitted transaction. Can someone explain?
>
> Session 1:
> ----------
> SQL> create table test1 (id number) tablespace tools;
>
> Table created.
>
> SQL> insert into test1 values (10);
>
> 1 row created.
>
>
> Session 2:
> ----------
> SQL> alter tablespace users read write; --> session hung. Waiting on
> enqueue.
>
>
> thanks.
>
> charlin
>

Session 2 knows only that session 1 has some data changes in progress - it doesn't know what they are, and could only discover that by walking backwards along the entire undo chain of every current transaction. Since that is not an efficient thing to do, it simply waits until al current transactions have committed.

Arguably, a transaction could register that it was changing a particular tablespace - but that would be an overhead on every single tiny task, to help out a job you are going to do very rarely, so the trade off is not worth it.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005