Re: Two "create table as select" locking?

First, thanks for all the responses. This is all good information. Let me answer some of these questions:

Jonathan Lewis wrote in message
<998641417.23317.1.nnrp-10.9e984b29_at_news.demon.co.uk>...
>
>Just to clarify this:
>
>Where the two users actually connected
>as the same Oracle user/schema.
>

No, two schemas.

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

This is v8.0.6, so tables are dictionary managed.

>Were the tables being created in the
>same tablespace ?
>

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

Autoextend was on, files were not full and they had room to autoextend.

>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)
>

No indexes or constraints were being created.

>Were the tables IOTs or heap tables ?
>

Heap

>Were the tables partitioned ?
>

The tables being selected from are partitioned. The tables being create are not.

>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.
>

The job completed before the DBA could hit 'refresh' on her OEM screen.

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

The create table comes back in less than a second when run alone.

>Is it reproducible ?
>

Maybe, but it is really hard to duplicate that timing.

I'll pass along the tip about looking at v$lock and dba_kgllock. Maybe that will provide a clue to what's going on.

Thanks again,
Greg Received on Fri Aug 24 2001 - 08:03:13 CDT