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
Received on Thu Jan 11 2007 - 18:48:20 CST
