/**
 sys 나 system 계정으로 sqlplus/toad 접속
 락걸린 테이블 확인
*/
SELECT vo.session_id,do.object_name, do.owner, do.object_type,do.owner, 
       vo.xidusn, vo.locked_mode 
FROM v$locked_object vo , dba_objects do 
WHERE vo.object_id = do.object_id

 

/**
 락걸린 테이블의 갯수
*/
SELECT count(*)
FROM v$locked_object vo , dba_objects do 
WHERE vo.object_id = do.object_id

 

/**
 락 걸린 세션 확인
*/
SELECT 'ALTER SYSTEM KILL SESSION ''' || A.SID || ', ' || A.SERIAL# || ''';' as script,  A.SID, A.SERIAL#, A.USER#, A.USERNAME, A.STATUS, A.SERVER, A.SCHEMANAME, A.MACHINE, A.PORT, A.TERMINAL, A.PROGRAM, C.OBJECT_NAME, OBJECT_TYPE
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';


/**
 sid,serial#를 이용하여 session kill
*/

alter system kill session 'sid, serial#'

 

ex) alter system kill session '13,20578'




Select /*+ rule */
           s.username Ora_User,
           osuser OS_User,
           s.sid sid ,
           s.serial# Serial#,
           p.spid,
           l.type Type,
           decode(lmode,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') Held,
           o.name Object_Name,
           id1 Object_Id,
           s.status Status,
           decode(request,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') Req,
           s.terminal
   From v$lock l, v$session s,
           sys.obj$ o ,
           v$process p
  Where l.sid = s.sid
      and s.paddr=p.addr
      and l.id1 = o.obj#(+)
      and s.username is not null
Order by sid