Re: I've now, officially, seen it all...

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Jan 2007 18:56:53 -0800
Message-ID: <1168570613.577466.324400@q2g2000cwa.googlegroups.com>

DA Morgan wrote:
> Charles Hooper wrote:
> > Andreas Sheriff wrote:
> >> Of course, the subject of this post is completely fictitious, so don't flame
> >> me. :-D
> >>
> >> Does anyone have any idea why someone in their right mind would need a SQL
> >> statement such as the following:
> >>
> >> SELECT NULL FROM DUAL FOR UPDATE NOWAIT
> >>
> >> Does this make sense? Did I actually loose it?
> >>
> >> Andreas
> >
> > A little test:
> >
> > Session 1:
> > SQL> SELECT NULL FROM DUAL FOR UPDATE NOWAIT;
> > N
> > -
> >
> > Session 2:
> > SQL> SELECT
> > 2 *
> > 3 FROM
> > 4 V$LOCKED_OBJECT;
> >
> > XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME
> > OS_USER_NAME PROCESS LOCKED_MODE
> > 6 18 14870 258 193 SYS hooper
> > 804:212 3
> >
> > SQL> UPDATE DUAL SET DUMMY='A';
> >
> > Session 2 hangs waiting for session 1 to commit or rollback.
> >
> > Session 1:
> > SQL> ROLLBACK;
> > Rollback complete.
> >
> > Session 2:
> > 1 row updated.
> >
> > SQL> ROLLBACK;
> > Rollback complete.
> >
> > This is obviously an attempt to allow only one session at a time to
> > update the DUAL table. :-)
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.

>

> And by locking DUAL you can prevent anyone from doing a delete or
> truncate: Brilliant!
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Curosity got the best of me. Take a look at Note:4179282.8 on Metalink. SQLPlus in Oracle 9.0 apparently issues "select null from dual for update nowait;" to determine if a database is read only.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 11 2007 - 20:56:53 CST