Oracle - LOCK 유저검색및 LOCK 풀기

-- ORA-28000: the account is locked 오류시
-- 여러 차례 로그인 실패시 또는 30일동안(Default) 해당 계정으로 로그인을 하지 않았을 경우 lock 걸림

1) Unix 시스템
 # sqlplus "/as sysdba"  로 접속

2) Windows 시스템
C:\>sqlplus system/비밀번호@접속DB명 as sysdba
-- 또는 --
C:\>sqlplus /nolog
SQL> conn system/비밀번호@접속DB명 as sysdba
-- 비밀번호는 아무거나 입력해도 통과됨.
연결되었습니다.
세션이 변경되었습니다.
SQL> show user
USER은 "SYS"입니다
SQL>

-- lock 걸린 User 찾기
SELECT username, account_status, to_char(lock_date,'yy/mm/dd hh24:mi') lock_date, profile
  FROM dba_users;
 
-- Lock 풀기 (유저가 system 일 경우)
ALTER USER SYSTEM account unlock;

-- 패스워드 변경
ALTER USER SYSTEM IDENTIFIED BY manager;

# 아래 내용 출처
http://blog.naver.com/steel_cat?Redirect=Log&logNo=40101605344

1. 먼저 lock 확인을 한다.

 -- lock 걸린 SERIAL_NO,SESSION_ID, OBJECT
SELECT DISTINCT t1.session_id AS session_id
               ,t2.serial# AS serial_no
               ,t1.os_user_name AS os_user_name
               ,t1.oracle_username AS oracle_username
               ,t2.status AS status
               ,t3.object_name
               ,DECODE( locked_mode
                       ,2, 'ROW SHARE'
                       ,3, 'ROW EXCLUSIVE'
                       ,4, 'SHARE'
                       ,5, 'SHARE ROW EXCLUSIVE'
                       ,6, 'EXCLUSIVE'
                       ,'UNKNOWN'
                      ) lock_mode
           FROM v$locked_object t1, v$session t2, dba_objects t3
          WHERE t1.session_id = t2.SID
            AND t1.object_id = t3.object_id;
 
2. 위 쿼리에서 확인한 session_id를 *SESSION_ID*에 넣으면 어떤 sql을 사용해서 lock이 걸렸는지 확인 할 수있다.

-- lock 걸린 sql
SELECT   b.username username
        ,c.SID AS session_id
        ,c.owner object_owner
        ,c.OBJECT OBJECT
        ,b.lockwait
        ,a.sql_text SQL
        ,piece
    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'
     and c.SID = '*SESSION_ID*'
ORDER BY b.username, c.SID, c.owner, c.OBJECT, piece;
  
3. 아래 쿼리 *SESSION_ID*,*SERIAL_NO* 부분에 위 1번 쿼리에서 확인한 session_id,serial_no을 사용하여 lock 걸린 session을 kill 한다.

 -- lock 걸린 SESSION kill
alter system kill session 'SESSION_ID ,SERIAL_NO' ;

4. 3번의 alter system kill session을 사용하여도 죽지 않는 경우 아래 쿼리를 사용하여 PROCESS ID를 확인하여 unix에서 직접 PROCESS를 kill한다.
   * 주의!!! 실수하기 쉽고 위험한 방법으로 lock걸린 내용을 정확히 알지 못할 경우 kill하지 말것!!!

 -- lock 걸린 PROCESS ID 찾기
SELECT DISTINCT s.username "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.SID
            AND p.addr = s.paddr
            AND s.username != 'SYS'

 -- unix에 로그인 하여 process kill
kill -9 프로세스아이디