You are herefind Session who Locking Object

find Session who Locking Object


By surachart - Posted on 29 December 2008

simple to Check someone who locking some objects on database

Session 1:
SQL> connect scott

SQL> select * from EMP where empno=7900 for update ;

Session 2:

SQL> connect / as sysdba

Find sid and other information on lock view with object_name.

select OBJECT_ID, OWNER,OBJECT_NAME ,l.sid , l.block from dba_objects o, V$LOCK l where l.ID1=o.OBJECT_ID and object_name = '&object_name' ;

SQL> select OBJECT_ID, OWNER,OBJECT_NAME ,l.sid , l.block from dba_objects o, V$LOCK l where l.ID1=o.OBJECT_ID and object_name = 'EMP' ;

OBJECT_ID OWNER OBJECT_NAM SID BLOCK
---------- ---------- ---------- ---------- ----------
51151 SCOTT EMP 142 2

Found session id = 142 , that's locking on EMP table.
find more information on v$session view.

After that can find process on OS (if use Unix/Linux).

select p.SPID from v$session s, v$process p where s.PADDR = p.ADDR and s.sid=&sid

SQL> select p.SPID from v$session s, v$process p where s.PADDR = p.ADDR and s.sid=142;

SPID
-----
17203

$ ps -aef | grep 17203

oracle 17203 17149 0 15:52 ? 00:00:00 oracledb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

surachart
http://surachart.blogspot.com

Tags


Distribuir

Distribuir contenido

Follow DatabasesLA on Twitter

En línea

En este momento hay 0 usuarios y %count invitados en línea.

Estadisticas

Locations of visitors to this page

hidden hit counter