락(lock)걸린 테이블 확인 및 해결

select
      count(1) 
from 
      v$locked_object vo
    , dba_objects do
 where vo.object_id = do.object_id;

[한줄표시] select count(1) from v$locked_object vo, dba_objects do where vo.object_id = do.object_id; 

select * from v$locked_object 
 where  oracle_username = '{접속계정명}';

select
      vs.sid
    , vs.serial# 
from 
      v$session vs
    , v$lock vl
    , dba_objects do
 where vs.sid = vl.sid and vl.id1=do.object_id and vl.type='TM';
>>> 락걸린 테이블명을 알고 있으면 아래 조건을 추가하면 된다.
    and do.object_name = '{table name}'

[한줄표시] select vs.sid,vs.serial# from v$session vs,v$lock vl, dba_objects do where vs.sid = vl.sid and vl.id1=do.object_id and vl.type='TM';

select 
     distinct vo.session_id
    ,vss.serial#
    ,do.object_name
    ,vss.machine
    ,vss.terminal
    ,vss.program
    ,vsql.address
    ,vsql.piece
    ,vsql.sql_text
from 
     v$locked_object vo
    ,v$session vss
    ,v$sqltext vsql
    ,dba_objects do
where 
        1=1
    and vo.session_id   = vss.sid
    and vo.object_id    = do.object_id
    and vss.sql_address = vsql.address
order by vsql.address, vsql.piece;

[한줄표시] select distinct vo.session_id,vss.serial#,do.object_name,vss.machine,vss.terminal,vss.program,vsql.address,vsql.piece,vsql.sql_text from v$locked_object vo,v$session vss,v$sqltext vsql,dba_objects do where 1=1 and vo.session_id = vss.sid and vo.object_id = do.object_id and vss.sql_address = vsql.address order by vsql.address, vsql.piece;

alter system kill session 'sid, serial#';
alter system kill session '1954, 63611';

ERROR at line 1:
ORA-00031: session marked for kill

select vs.sid,vs.username,vs.osuser,vs.process fg_pid,vp.spid bg_pid from v$session vs,v$process vp where vs.paddr=vp.addr;

# KILL -9 {PID}