Re: Two "create table as select" locking?

Greg Simpson wrote:
>
> 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?

There should not be any locking nor blocking by doing this. The issue, however, may be the heavy I/O load going on as both CTAS statements do full table scans against the same table (and same controllers - compounded if both new tables are being created in the same tablespace as the original table). How large are the tables? And how are your disks set up?

Cheers,
GC Received on Thu Aug 23 2001 - 18:18:37 CDT