[운영]Lock Object 해결(미완)

lock 걸린 object 확인

select sid,        serial#,        username,        module,        blocking_session,        sql_id,        sql_exec_start,        status,        prev_sql_id,        event,        b.object_id        ,(select object_name from dba_objects o where o.object_id=b.object_id) object_name,        (select sql_text from v$sql q where q.sql_id=s.sql_id) sqltext   from v$session s, v$locked_object b  where s.sid=b.session_id;   select * from dba_dml_locks;   select * from dba_lock where lock_type in (‘Transaction’);

 

lock mode

select sid, type, lmode from v$lock where type=’TM’; select object_id, session_id, locked_mode from v$locked_object;

 

현재 상황 해결 ( session kill )

<일반적인 상황> 1.     현재 blocking session과 waiting session을 확인한다. 2.     고객의 요구에 따라 현재 blocking session의 kill, commit, rollback을 결정   <현재 문제상황 해결> 1.    현재 blocking session을 확인하고 kill 2-1. 현재 blocking session만 kill > 해당 lock걸린 object의 가장 먼저 실행된 wating session이 blocking session이 된다 2-2. 현재 blocking session과 관련된 모든 wait session을 kill

 

blocking session 찾기

1개   select sid,        serial#,        status,        (select sql_fulltext from v$sql where sql_id=a.prev_sql_id) sqltext,        prev_exec_start   from v$session a  where sid in (select blocking_session from v$session );   select * from dba_writers;

 

session이 수행한 sql

--blocking session의 수행 sql select sid,        serial#,        status,        (select sql_fulltext from v$sql where sql_id=a.prev_sql_id) sqltext,        prev_exec_start   from v$session a  where sid in (select blocking_session from v$session );   --waiting session의 수행 sql select sid,        serial#,        username,        module,        blocking_session,        sql_id,        sql_exec_start,        status,        prev_sql_id,        event,        b.object_id        ,(select object_name from dba_objects o where o.object_id=b.object_id) object_name,        (select sql_text from v$sql q where q.sql_id=s.sql_id) sqltext   from v$session s, v$locked_object b  where blocking_session is not null    and s.sid=b.session_id;