Re: Why can't I truncate table?

Bill,  

  If you don't mind changing the code that does the insert and truncate   to add some house keeping statments before and after then you can   try using the package dbms_lock.request(...).

  for insert ...

   dbms_lock.allocate_unique('xyz', :lockhandle);
   dbms_lock.request(:lockhandle, 3, maxwait, false); 
   insert ....

  for truncate ...

 1.lock table xyz in exclusive mode;
   dbms_lock.allocate_unique('xyz', :lockhandle);    dbms_lock.request(:lockhandle, 6, maxwait, true);

 2.select 
 3.truncate table xyz
 4.dbms_lock.release(:lockhandle)

 This solution will prevent your session from failing with a nowait  problem.

 BTW, delete does not hold a dictionary lock. Any DDL statements  that requires an exclusive DDL lock will wait on any type of  table lock whether it be DDL or DML.

 Clement

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 Mon Mar 15 1999 - 05:57:44 CST