Xref: alice comp.databases.oracle.server:43149
Path: alice!news-feed.fnsi.net!WCG!newsfeed.berkeley.edu!intgwpad.nntp.telstra.net!vic.nntp.telstra.net!news.mira.net.au!newsreader.mira.net.au!dingo.mira.net!not-for-mail
From: schong <schong@werple.mira.net.au>
Newsgroups: comp.databases.oracle.server
Subject: Re: Why can't I truncate table?
Date: Mon, 15 Mar 1999 03:57:44 -0800
Organization: Internet News services provided by Mira Networking, Australia
Lines: 57
Message-ID: <36ECF5B8.7DFE@werple.mira.net.au>
References: <7cgpan$jpj$1@news.vassar.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 3.04Gold (Win16; I)

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