Re: Why can't I truncate table?

Hi Bill,
This is indeed an interesting one! The explanation however is probably TRUNCATE is an DDL-operation as amongst others it resets the table high water mark. Thus the session needs to acquire an exclusive DICTIONARY lock on the object. However the delete also acquires a dictionary lock (as with any other sql-statement, no DDL can be issued on a table, when it is involved in a select or insert or update or delete), and this prevents the truncate from proceeding. As truncate looks like a DDL operation it will also automatically commit the transaction. The lock you acquire is an exclusive lock on the table, not on the dictionary. Right now I don't see any method to prevent this.

Hth anyway,

Sybrand Bakker, Oracle DBA

Bill Wine wrote:

> I want to lock a table to prevent changes, do some read-only
> processing on rows in the table, then truncate the table,
> and commit, e.g.
>
> 1. lock table xyz in exclusive mode;
> 2. select ... from xyz;
> 3. truncate table xyz;
> 4. commit;
>
> I find (Oracle 7.3.3.4) that if another user attempts to insert
> into xyz after step 1, but before step 3, the truncate fails with:
>
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> The other user's insert then succeeds.
>
> If I 'delete from xyz' instead of 'truncate xyz' it works as expected.
>
> Why can't the process that locks the table in exclusive mode then
> do a truncate when another process is waiting to insert?
>
> Thanks for your help.
>
> Bill

Received on Sun Mar 14 1999 - 15:32:36 CST