[Oracle] Oracle Lock 확인 및 제거

내가 알고 있는 SQL

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.command, 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
ORDER BY 6;

Oracle Lock 확인 및 제거

-- 락걸린 테이블 확인

SELECT do.object_name, do.owner, do.object_type, do.owner, vo.xidusn, vo.session_id, vo.locked_mode FROM v$locked_object vo ,  dba_objects do
WHERE vo.object_id = do.object_id ;

--해당테이블이 락에 걸렸는지..

SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID=B.SID AND B.ID1=C.OBJECT_ID AND B.TYPE='TM' AND C.OBJECT_NAME IN ('테이블명');

 /* 락발생 사용자와 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 x.object_id = d.object_id and a.sql_address = b.address
ORDER BY b.address, b.piece;

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

SELECT distinct x.session_id, a.serial#, d.object_name, a.machine, a.terminal, a.program, a.logon_time , '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;

 /* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '26,6044';

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

SELECT distinct a.sid, a.serial#, 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.serial#, b.address, b.piece;

[출처] oracle lock 확인 및 제거 |작성자 motbombe