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

On Jan 11, 9:56 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.orgCurosity 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.- Hide quoted text -- Show quoted text -

You would think that the "read only" status for a database would be recorded in an X$ storage location and that querying it would be both more efficient and more clean as to the purpose of the statement. In fact I would think that v$database.open_mode would be usuable to indicate this though I do not have a system available for testing.

IMHO -- Mark D Powell -- Received on Fri Jan 12 2007 - 09:38:17 CST