How to Check Whether an AOL Table is Locked

The sql statements below will help to determine if there is
a lock on the AOL table.


Checking for locked files
=========================

To check whether an AOL table is locked or not:

Sign on to sqlplus as the APPS account.

The example that is being used here is an attempt to modify a user's password
in System Administrator Responsibility, Users Define Form
(Navigation path: Security -> User -> define).

After modifying the password of a user in the User Define Screen:

SQL> select * from v$lock;

  .... take note of the SID number (using '33' for this example)

SQL> select ADDR, KADDR, SID, TYPE, ID1, ID2, LMODE, REQUEST 
    from v$lock  where sid=33;

   Here's an example.

ADDR       KADDR      SID TY        ID1        ID2  LMODE  REQUEST
---------- ---------- --- -- ---------- ---------- ------ --------
010AB98C   010ABA58    33 TX     196618     107158      6        0
803CE1D8   803CE1EC    33 TM       4136          0      2        0

v$lock.type:
  TX: Transaction enqueue
  TM: DML enqueue

v$lock.lmode:  Lock mode in which the session holds the lock:
  0 means none
  1 means null
  2 means Row-S (SS)
  3 means Row-X (SX)
  4 means Share (S)
  5 means S/Row-X (SSX)
  6 means Exclusive (X)

v$lock.request: Lock mode in which the process requests the lock.

To find out which table is being locked by TM type lock:
(v$lock.id1 = dba_objects.object_id when v$lock.type = TM)

SQL> select object_name, owner from dba_objects where object_id=4136;

OBJECT_NAME                    OWNER
------------------------------ ---------------
FND_USER                       APPLSYS

 
To list the object which is being accessed by the session id 33:

SQL> select * from v$access  where sid =33;

SID OWNER           OBJECT                         TYPE
--- --------------- ------------------------------ ------------------------
33 APPLSYS         ALR_PROFILE_OPTIONS            TABLE
33 APPLSYS         FND_APPLICATION                TABLE
33 APPLSYS         FND_ATTACHED_DOCUMENTS         TABLE
33 APPLSYS         FND_ATTACHMENT_FUNCTIONS       TABLE
33 APPLSYS         FND_CONCURRENT_PROGRAMS        TABLE
33 APPLSYS         FND_CONCURRENT_REQUESTS        TABLE
33 APPLSYS         FND_CONCURRENT_REQUESTS_S      SEQUENCE
33 APPLSYS         FND_CONC_REQUEST_ARGUMENTS     TABLE
33 APPLSYS         FND_DATA_GROUP_UNITS           TABLE
33 APPLSYS         FND_DOCUMENTS                  TABLE
33 APPLSYS         FND_DOC_CATEGORY_USAGES        TABLE
33 APPLSYS         FND_FORM                       TABLE
33 APPLSYS         FND_FORM_FUNCTIONS             TABLE
33 APPLSYS         FND_LANGUAGES                  TABLE
33 APPLSYS         FND_LOGINS                     TABLE
33 APPLSYS         FND_LOGINS_S                   SEQUENCE
33 APPLSYS         FND_LOGIN_RESPONSIBILITIES     CURSOR
33 APPLSYS         FND_LOGIN_RESP_FORMS           TABLE
33 APPLSYS         FND_LOOKUP_VALUES              TABLE
33 APPLSYS         FND_ORACLE_USERID              TABLE
33 APPLSYS         FND_PRINTER                    TABLE
33 APPLSYS         FND_PRINTER_INFORMATION        TABLE
33 APPLSYS         FND_PRINTER_STYLES             TABLE
33 APPLSYS         FND_RESPONSIBILITY             TABLE
33 APPLSYS         FND_SESSIONS                   CURSOR
33 APPLSYS         FND_USER                       TABLE
33 APPLSYS         FND_USER_RESPONSIBILITY        TABLE
33 APPLSYS         FND_USER_S                     SEQUENCE
.....
111 rows selected

To find out what is the last SQL statement issued:

SQL> select a.sql_text
 2  from v$sqltext a, v$session b
 3  where a.address = b.sql_address
 4  and sid=33
 5* order by piece;

SQL_TEXT
----------------------------------------------------------------
SELECT USER_NAME,DESCRIPTION,EMAIL_ADDRESS,FAX,PASSWORD_LIFESPAN
_DAYS,PASSWORD_LIFESPAN_ACCESSES,START_DATE,END_DATE,EMPLOYEE_ID
,PERSON_TYPE,USER_ID,ENCRYPTED_FOUNDATION_PASSWORD,PASSWORD_DATE
,PASSWORD_ACCESSES_LEFT,ENCRYPTED_USER_PASSWORD,SESSION_NUMBER,L
AST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY,CREATION_DATE,LAST_UP
DATE_LOGIN FROM FND_USER WHERE ROWID=:1 FOR UPDATE OF USER_NAME
NOWAIT

7 rows selected.

Note: If you reissue the above sql statement after saving the changes
     in the user define screen, you will see the following output:

SQL_TEXT
----------------------------------------------------------------
UPDATE FND_USER SET USER_NAME=:1,DESCRIPTION=:2,EMAIL_ADDRESS=:3
,FAX=:4,PASSWORD_LIFESPAN_DAYS=:5,PASSWORD_LIFESPAN_ACCESSES=:6,
START_DATE=:7,END_DATE=:8,EMPLOYEE_ID=:9,PERSON_TYPE=:10,USER_ID
=:11,ENCRYPTED_FOUNDATION_PASSWORD=:12,PASSWORD_DATE=:13,PASSWOR
D_ACCESSES_LEFT=:14,ENCRYPTED_USER_PASSWORD=:15,SESSION_NUMBER=:
16,LAST_UPDATE_DATE=:17,LAST_UPDATED_BY=:18,CREATED_BY=:19,CREAT
ION_DATE=:20,LAST_UPDATE_LOGIN=:21 WHERE ROWID=:22

7 rows selected.


To list the processes involved:

SQL> select distinct s.username, p.pid, osuser, p.spid, s.process, s.lockwait
 2  from v$process p, v$session s, v$access a
 3  where a.sid = s.sid
 4  and p.addr = s.paddr
 5  and s.username in ('APPS','APPLSYS')
 6* and s.sid = 33;

USERNAME        PID OSUSER          SPID      PROCESS   LOCKWAIT
-------- ---------- --------------- --------- --------- --------
APPS             11 OraUser         7228      5791:01

SQL> select addr, pid, spid, username, serial#, program, latchwait
 2* from v$process where pid = 11;

ADDR      PID SPID  USERNAME SERIAL# PROGRAM                   LATCHWAI
-------- ---- ----- -------- ------- ------------------------- --------
8018B174   11 7228  oracle7      100 oracle@suppue2(TNS V1-V3)

SQL> select distinct s.username, p.pid, osuser, p.spid, s.process, s.lockwait
 2  from v$process p, v$session s, v$access a
 3  where a.sid = s.sid
 4  and p.addr = s.paddr
 5  and s.username in ('APPS','APPLSYS')
 6* and s.sid = 33

USERNAME        PID OSUSER          SPID      PROCESS   LOCKWAIT
-------- ---------- --------------- --------- --------- --------
APPS             11 OraUser         7228      5791:01

In the above example the LATCHWAIT and LOCKWAIT columns are blank
indicating there is no lock waiting.


At times, it may be difficult to find out the root cause of the "hanging"
problem within the application. The best starting point for this type of
issue is the v$session_wait view.