====== Oracle Table Lock 처리방법 ======

  * ORA-00054 (resource busy and acquire with NOWAIT specified) 에러는 commit/rollback을 처리를 안했거나, 특정작업을 하고 있었거나 그 이유는 매우 다양하다.
  * DB를 재시작할 수 없는 상태에서는 Lock이 걸린 세션을 kill 해주어야 한다.

===== Table Lock kill mini-HOWTO =====

  - lock 걸린 테이블 검색<code sql>
select a.sid , a.serial#
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='TABLE_NAME';
     SID    SERIAL#
------- ----------
    1004       10001
</code>
  - Lock 걸린 세션을 kill 시킨다.<code sql>
alter system kill session '1004,10001';
</code>

===== Lock발생 사용자와 sql, object 조회 =====
<code sql>
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
--and a.terminal = ''
order by b.address,b.piece;
</code>

=====  Lock 발생 사용자확인 =====
<code sql>
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;
</code>

===== 현재 접속자의 sql 분석 =====
<code 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;
</code>

===== 접속 사용자 세션 제거 =====
<code sql>
--alter system kill session 'session_id,serial#';
alter system kill session '1004,10001'; 
</code>


===== Lock waiting 세션의 SQL & TABLE & ROWID 구하기 =====
  - howto 1 <code sql>
select a.sid, 
       a.serial#, 
       p.spid, 
       a.status,
       b.sql_text,
       c.object_type ,
       c.owner||'.'||c.object_name object, 
       sys.dbms_rowid.rowid_create(1,                   -- rowid type : 0(restricted), 1(extended)
                                   a.row_wait_obj#,              -- object_number
                                   a.row_wait_file#,               -- releative file number
                                   a.row_wait_block#,            -- block number
                                   a.row_wait_row#) row_id  -- file number
from v$session a, v$process p, v$open_cursor b, dba_objects c
where a.sql_address = b.address
and a.paddr = p.addr
and a.row_wait_obj# = c.object_id
and a.lockwait is not null

 

 SID SERIAL# SPID STATUS   SQL_TEXT                                                OBJECT_TYPE  OBJECT    ROW_ID
 --- --- ------  -------  -----------------------------------------------   --------  ---------       ------------------
 10  19  4056    ACTIVE   update emp set sal = '2000' where empno =  :b1         TABLE   SCOTT.EMP   AAAHW7AABAAAMUiAAB
 16  255 2816   ACTIVE   delete emp where empno =  7499                               TABLE   SCOTT.EMP   AAAHW7AABAAAMUiAAB
 17  5   1356    ACTIVE   update emp set job = 'CLERK' where empno = 7698     TABLE   SCOTT.EMP   AAAHW7AABAAAMUiAAF


<Lock row find>

select * from emp rowid = 'AAAHW7AABAAAMUiAAB'
 
</code>
  - howto 2 <code sql>
select /*+ ordered */
       a.sid,
       a.serial#,
       p.spid,
       a.status,
       l.type,
       decode(l.lmode, 0, 'wait', 'hold') stat,
       decode(l.lmode,
              0,              'none',
              1,              'null',
              2,              'row share',
              3,              'row excl.',
              4,              'share',
              5,              's/row excl.',
              6,              'exclusive',
              ltrim(to_char(l.lmode, '990'))) lmode,
       decode(l.request,
              0,              'none',
              1,              'null',
              2,              'row share',
              3,              'row excl.',
              4,              'share',
              5,              's/row excl.',
              6,              'exclusive',
              ltrim(to_char(l.request, '990'))) request,
       l.id1,
       l.id2,
       a.module,
       a.machine,
       decode(a.sql_address, '00', a.prev_sql_addr, a.sql_address) as sql_address,
       decode(a.sql_hash_value, '0', a.prev_hash_value, a.sql_hash_value) as hash_value,
       b.sql_text,
       c.object_type,
       c.owner || '.' || c.object_name object,
       decode(a.lockwait, null, 'n/a',
              sys.dbms_rowid.rowid_create(1,
                                          a.row_wait_obj#,
                                          a.row_wait_file#,
                                          a.row_wait_block#,
                                          a.row_wait_row#)) row_id
  from v$lock l, v$session a, v$process p, v$open_cursor b, dba_objects c
 where ((a.sid = l.sid and l.request <> 0) or
       (a.sid = l.sid and l.request = 0 and l.lmode <> 4 and
       (l.id1, l.id2) in (select s.id1, s.id2
                              from v$lock s
                             where s.request <> 0
                               and s.id1 = l.id1
                               and s.id2 = l.id2)))
   and decode(a.sql_address, '00', a.prev_sql_addr, a.sql_address) =
       b.address
   and a.sid = b.sid
   and a.paddr = p.addr
   and a.row_wait_obj# = c.object_id(+)
 order by l.id1, l.id2, l.request;
</code>

===== LOCK을 HOLDING 하고 있는 세션의 SQL이 보이지 않을 경우 =====
<code sql>
select * from v$open_cursor where sid = 12  -- HOLDING세션
</code>