ORACLE LOCK 관련 조회쿼리

/* Lock 조회 */

select a.sid, a.serial#, a.username, a.process, substr(a.MACHINE, 1, 20), to_char(a.LOGON_TIME, 'yyyymmddhh24miss'),

b.object_name, decode(c.lmode, 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 8, 'X', 'NO') TABLE_LOCK,

decode(a.commend, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 12, 'DROP', 6, 'LOCK', 'unknown') SQL,

decode(a.lockwait, NULL, 'No Wait', 'Wait') STATUS

from v$session a, dba_objects b, v$lock c

where a.sid = c.sid

and b.object_id = c.id1

and c.type = 'TM'

order by 6

/* Lock 발생 사용자와 sql, object조회 */

select distinct x.session_id, a.serial#, d.object_name, a.machine, a.terminal, a.program, b.address, b.piece, b.sql_text

from v$locked_object x, v$session a, v$sqltext b, dba_objects d

where x.session_id = a.sid

and a.sql_address = b.address

-- and a.terminal = '김성기'

order by b.address, b.piese;

/* Lock 발생 사용자 확인 */

select distinct x.session_id, a.serial#, d.object_name, a.machine, a.terminal, a.program, a.logon_time

--select 'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'

from gv$locked_object x, gv$session a, dba_objects d

where x.session_id = a.sid

and x.object_id = d.object_id

order by logon_time;

/* 현재 접속자의 sql 분석 */

select distinct a.sid, a.sirial#, a.machine, a.terminal, a.program, b.address, b.piece, b.sql_text

from v$session a, v$sqltext b

where a.sql_address = b.address

order by a.sid, a.sirial#, b.address, b.piece

/* 접속 사용자 제거 */

-- alter system kill session 'session_id,serial#'

alter system kill session '27,29915'