RE: Script to overcome ORA-54 during DDL

Thank you Jonathan - you have prompted me to find out why.

session 1
        create table dmk1(a number);
        insert into dmk1 values(1);
        create table dmk2(a number);
        insert into dmk2 values(1);
        commit;

session 2
        update dmk1 set a=2;
        --so now we hold a lock

back to session 1

        update dmk2 set a=2;
        alter table dmk1 modify a not null;
        ORA-00054: resource busy and acquire with NOWAIT specified
        rollback;
        select * from dmk2;

                 A
        ----------
                 2

        --ouch! a DDL that fails with ORA-54 has already issued an implicit 
commit

So the DDL commits, releasing the exclusive lock, and then tries to acquire
the lock again in nowait mode, presumably queuing up behind any else who
requested a lock between my requesting and subsequently acquiring and
releasing the exclusive lock?







_________________________
David Kurtz

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: 26 June 2004 08:19
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Script to overcome ORA-54 during DDL



The drawback to locking the table before
doing the DDL is that any other user wanting
to do DML will be queueing behind your lock
request.  So in busy systems this is likely to
increase the probability that someone will block
you in the moments between the implicit commit
and the start of the DDL.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message -----
From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, June 25, 2004 10:59 PM
Subject: RE: Script to overcome ORA-54 during DDL


I've used something like this in the past, except I also exclusively lock
the table to which I am trying to apply the DDL.
The idea being that I wait to get the exclusive lock, and having got the
exclusive lock the DDL can acquire the lock in nowait mode.  Its not
perfect, but I find I need less attempts to get the DDL to execute.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: