Oracle DML locks - a very simple test

V$LOCK

Column: type
  Possible values:
  TM - DML or Table Lock
  TX - transaction
  MR - Media Recovery
  ST - Disk Space Transaction

Column: lmode, request
  If lmode is not 0 or 1, then the session has aquired the lock;
  If request is other than 0 or 1, the session is waiting to aquire lock
  Possible values:
  1: null
  2: Row Share (SS)
  3: Row Exclusive (SX)
  4: Share (S)
  5: Share Row Exclusive (SSX)
  6: Exclusive(X)


Steps:

1. Obtain current session SID


SQL> select sid from v$session where audsid=userenv('SESSIONID');

       SID
----------
        27

2. Insert a row to the table lck

SQL> insert into lck values(30,31);

1 row created.

SQL> select * from lck;

         A          B
---------- ----------
         1          2
         2          3
         4          5

3. How does step 2 influence v$lock?

SQL> select sid,type,id1,lmode,request from v$lock where sid = 27;

       SID TY        ID1      LMODE    REQUEST
---------- -- ---------- ---------- ----------
        27 TX     327746          6          0
        27 TM      16147          3          0


Session 27 has acquired two locks.


4. At this time, another session tyring to do DDL to lck, what will happen?

SQL> alter table lck add (c number);
alter table lck add (c number)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified