Find Oracle Database session from OS process

Execute the following query and find out the Database session

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and  b.username is not null
order by a.spid asc;  2    3    4    5

SERVER                   CLIENT                   USERNAME                       STATUS
------------------------ ------------------------ ------------------------------ --------
14674                    2868:6052                SYS                            INACTIVE
14679                    2868:4792                SYS                            INACTIVE
14754                    14753                    SYS                            ACTIVE

SQL>

Execute the following command and find out the OS process mapping with Database session

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL

oracle   14674  0.0  1.0 1682420 48532 ?       Ss   11:13   0:00 oracleorcl (LOCAL=NO)
oracle   14679  0.0  0.4 1679844 21852 ?       Ss   11:13   0:00 oracleorcl (LOCAL=NO)
oracle   14754  0.0  0.4 1679844 23068 ?       Ss   11:20   0:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@saidrasel ~]$

Now create three new connection in Database and execute the query

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and  b.username is not null
order by a.spid asc  2    3    4    5  ;

SERVER                   CLIENT                   USERNAME                       STATUS
------------------------ ------------------------ ------------------------------ --------
14674                    2868:6052                SYS                            INACTIVE
14679                    2868:4792                SYS                            INACTIVE
14754                    14753                    SYS                            ACTIVE
14785                    2868:4792                SAID                         INACTIVE
14787                    2868:4792                SAID                         INACTIVE
14790                    3944:3764                RASEL                  INACTIVE

6 rows selected.

SQL>

Now find the these Database session from OS process also

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL
oracle   14674  0.0  1.0 1682420 48540 ?       Ss   11:13   0:00 oraclestlbas (LOCAL=NO)
oracle   14679  0.0  0.4 1679844 21852 ?       Ss   11:13   0:00 oraclestlbas (LOCAL=NO)
oracle   14754  0.0  0.4 1679844 23096 ?       Ss   11:20   0:00 oraclestlbas (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   14785  0.1  0.8 1682420 40408 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
oracle   14787  0.1  0.8 1680868 42848 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
oracle   14790  0.0  0.4 1679844 22528 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
[oracle@saidrasel ~]$

Find Oracle Database Lock using SQL Query

Following query find the user who is lock since last 120 second and locked by whom

SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 120
AND h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert desc;

Following query will find the user who is locking and locked by whom----

select

s.sid "SID",

s.serial# "SER",

o.object_name "Table",

s.osuser "OS User",

s.machine "Node",

s.terminal "Terminal",

p.spid "SPID",

s.process "CPID",

decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') "Mode",

substr (c.sql_text, 1, 500) "SQL Text"

from v$lock l,

v$lock d,

v$session s,

v$session b,

v$process p,

v$transaction t,

sys.dba_objects o,

v$open_cursor c

where l.sid = s.sid

and o.object_id (+) = l.id1

and c.hash_value (+) = s.sql_hash_value

and c.address (+) = s.sql_address

and s.paddr = p.addr

and d.kaddr (+) = s.lockwait

and d.id2 = t.xidsqn (+)

and b.taddr (+) = t.addr

and l.type = 'TM'

group by

o.object_name,

s.osuser,

s.machine,

s.terminal,

p.spid,

s.process,

s.sid,

s.serial#,

decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>'),

substr (c.sql_text, 1, 500)

order by

decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') desc,

o.object_name asc,

s.sid asc;

Kill session using following statement---

alter system kill session 'SID,SERIAL#';