Re: Two "create table as select" locking?

Just to clarify this:

Where the two users actually connected
as the same Oracle user/schema.

Were the tables being created in a
locally managed tablespace or dictionary managed tablespace.

Were the tables being created in the
same tablespace ?

Were the files of the tablespaces
AUTOEXTEND files - and were they
full before you started ?

Where the tables being created with
a declared primary/unique key - with
user generated name or system generated
name. (And what about tablespaces for
indexes)

Were the tables IOTs or heap tables ?

Were the tables partitioned ?

Did the one that completed really complete 'immediately' or did it complete a few seconds later - i.e. were the two jobs waiting to start, or were they waiting to finish.

How long should it take to create one of the tables on its own ?

Is it reproducible ?

In principle, what you saw should not have happened. Obviously Oracle generates
locks on data dictionary items as it creates tables (insert into tab$, obj$, col$ - update tsq$ etc.), but if these interfere you should get either a brief wait, or a deadlock in under 3 seconds. (And I have seen cases where deadlock do happen because of illogical recursive SQL). Similarly, CTAS does pin objects in the
library cache, but again you should get
only brief interference, or deadlocks (ORA-04020 rather than ORA-00060 in this case) within 3 seconds.

If it happens again - check v$lock for blockers, and dba_kgllock (generated by catblock.sql) for the library cache locks for the two SIDs in question. This may give you some clues

--
Jonathan Lewis

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

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.




Greg Simpson wrote in message ...


>Recently, a fellow DBA ran into a situation where two users issued a

"create


>table as select * from A" at the exact same time. Neither job returned

after


>an hour. She killed one of the jobs and the other finished immediately.

>Question: did the two "create table as select * from A" commands lock each

>other out? I know two "select * from A" should never lock each other, but

>does the "create table as" request a different type of lock?

>

>Thanks,

>Greg

>

>

>