Wednesday, November 7, 2007

How to Check Whether an AOL Table is Locked

Print this post
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.

No comments: