ロック情報：ハマオ備忘録：SSブログ

	ブログをはじめるログイン

ロック情報　[Oracle SQL] [編集]

lmode BETWEEN 2 AND 6 --> ロックをかけているほう
request BETWEEN 2 AND 6 --> 待機しているほう
両者をid1, id2で紐付ける事ができる。

SELECT a.inst_id
,a.sid
,s.serial#
,a.type
,l.name AS type_name
,a.lmode
,a.request
,a.id1
,a.id2
,a.ctime
,a.block
,s.logon_time
,s.process AS cpid
,p.spid
,s.status
,s.module
,s.action
,c.command_name AS command
,s.sql_id
,s.machine
,s.username
,s.blocking_session
,s.row_wait_obj# AS object_id
,s.sql_child_number
,s.sql_exec_start
,s.event
,s.p1
,s.p2
,s.p3
,o.owner
,o.object_name
,o.subobject_name
,o.object_type
,r.request_id
FROM gv$lock a
,gv$session s
,gv$process p
,gv$sqlcommand c
,gv$lock_type l
,dba_objects o
,apps.fnd_concurrent_requests r
WHERE a.type NOT IN ('MR', 'AE')
AND (a.inst_id,a.id1,a.id2) IN (SELECT b.inst_id,b.id1,b.id2
FROM gv$lock b
WHERE b.request BETWEEN 2 AND 6
AND b.type NOT IN ('MR', 'AE')
)
AND a.inst_id = s.inst_id
AND a.sid = s.sid
AND p.inst_id = s.inst_id
AND p.addr = s.paddr
AND s.inst_id = c.inst_id(+)
AND s.command = c.command_type(+)
AND a.inst_id = l.inst_id
AND a.type = l.type
AND s.row_wait_obj# = o.object_id(+)
AND s.audsid = r.oracle_session_id(+)
;