Re: ORA-00054: resource busy and acquire with NOWAIT specified

With 0 rows deleted:

       Oracle              Database
Lock                  Mode            Mode       OS                 OS
   SID Usernam  WATR  BLKR Object                    COMMAND         Type
Lock Description Held            Requested  Program            Process
------ ------- ----- ----- ------------------------- --------------- ----
---------------- --------------- ---------- ------------------ -------
   143 JS00129             JS001292.TL2              BACKGROUND      TM
DML enqueue lock Row-X (SX)      None       sqlplus_at_orcl. 26720

With 1 row deleted:

       Oracle              Database
Lock                  Mode            Mode       OS                 OS
   SID Usernam  WATR  BLKR Object                    COMMAND         Type
Lock Description Held            Requested  Program            Process
------ ------- ----- ----- ------------------------- --------------- ----
---------------- --------------- ---------- ------------------ -------
   143 JS00129             .                         BACKGROUND      TX
Transaction enqu Exclusive       None       sqlplus_at_orcl. 26720
   143 JS00129             JS001292.TL2              BACKGROUND      TM
DML enqueue lock Row-X (SX)      None       sqlplus_at_orcl. 26720

2 rows selected.

Oracle is taking a DML lock regardless of whether any rows were actually deleted.
A transaction has been started, and must be committed or rolled back before the
other session can truncate the table.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

On Tue, Oct 27, 2009 at 2:09 PM, dd yakkali <dd.yakkali_at_gmail.com> wrote:

> Hello gurus,
>
> I got this error and I want to confirm my concepts are right.
>
> Session 1
>
> delete from tab1 where col1=20;
> 0 rows deleted
>
> session2
> truncate table tab1;
> I get "ORA-00054: resource busy and acquire with NOWAIT specified"
>
>
> When session1 deleted 0 records, it should not be placing any locks on
> the table correct? I do not understand why I got the error. Am I
> missing some basic concept?
>
> When I did a rollback on the session1 then session2 was able to
> truncate the table.
>
>
> Thanks
> Deen
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l