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

1. lock 걸린 테이블 검색

   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

2. Lock 걸린 세션을 kill 시킨다.

   ALTER system KILL SESSION '1004,10001';

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;

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;

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;

--alter system kill session 'session_id,serial#';
ALTER system KILL SESSION '1004,10001'; 

1. howto 1

   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'
    

2. howto 2

   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;

SELECT * FROM v$open_cursor WHERE sid = 12  -- HOLDING세션