2012. 2. 23. 10:16

[Oracle] LOCK 해제

##  SESSION 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';

▶  ALTER SYSTEM KILL SESSION 'SID, SERIAL';


##  USER ACCOUNT LOCK 해제  ##

SELECT O.OBJECT_NAME
  FROM DBA_OBJECTS O, V$LOCKED_OBJECT OB
 WHERE O.OBJECT_ID = OB.OBJECT_ID;

SELECT B.USERNAME USERNAME, C.SID SID, C.OWNER OBJECT_OWNER,
       C.OBJECT OBJECT, B.LOCKWAIT, A.SQL_TEXT SQL
  FROM V$SQLTEXT A, V$SESSION B, V$ACCESS C
 WHERE A.ADDRESS=B.SQL_ADDRESS
   AND A.HASH_VALUE=B.SQL_HASH_VALUE
   AND B.SID = C.SID AND C.OWNER != 'SYS';

SELECT SUBSTR(S.USERNAME,1,11) "ORACLE USER", P.PID "PROCESS ID",
       S.SID "SESSION ID", S.SERIAL#, OSUSER "OS USER",
       P.SPID "PROC SPID",S.PROCESS "SESS SPID", S.LOCKWAIT "LOCK WAIT"
  FROM V$PROCESS P, V$SESSION S, V$ACCESS A
 WHERE A.SID=S.SID0
   AND P.ADDR=S.PADDR
   AND S.USERNAME != 'SYS'
   AND S.LOCKWAIT IS NOT NULL;

▶  ALTER USER USER_NAME ACCOUNT UNLOCK;

'Database / Sql' 카테고리의 다른 글

[Oracle] DBA_USER 확인  (0) 2012.02.23
[Oracle] SESSION 수 체크  (0) 2012.02.23
[Oracle] LOCK 해제  (0) 2012.02.23
[Oracle] 쿼리문 처리 과정  (0) 2012.02.16
[Oracle] USER_SOURCE  (0) 2011.12.27
[MS-SQL] CONVERT를 이용한 날짜 형식 변경  (1) 2011.12.14
Trackback 0 Comment 0