Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Two "create table as select" locking?
Thanks for the info, but I don't think this is the same situation. One user
would be running:
user1> create table X as select * from table A where ....;
and the other:
user2> create table Y as select * from A where ....;
The 'WHERE' clauses are similar and may be generating the same plan (didn't look), but by the time Oracle is at the 'SELECT' part of the procedure, what would be locking table A?
The X and Y tables are being created in the same tablespace. Would the CREATE step be getting locks on the data dictionary?
Thanks,
Greg
Anatoly Moskovsky wrote in message ...
>Hi!
>
>
>When you run "create table as" statement Oracle runs implicit
>"create","select", and "insert" statements.
>Dead lock is possible if two sessions insert rows in different
order(primary
>key must be enabled):
>SQL1> create table tmp1 (n number primary key);
>SQL1> insert into tmp1 values (1);
>SQL2> insert into tmp1 values (2);
>SQL1> insert into tmp1 values (2); --
>now SQL1 is waiting
>SQL2> insert into tmp1 values (1);
>now SQL2 is waiting but for SQL1 Oracle detects a dead-lock and unlock the
>session (SQL1)
>IMHO, this detection works with expicit inserts, but maybe does not work
>with implicit one such as "create table as select...".
>
Received on Thu Aug 23 2001 - 13:59:57 CDT