HighLight in your life~

/************************
DB 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='EMP';

alter system kill session '1903,692' immediate;

SELECT * FROM V$SESSION WHERE SID IN ('2821','7057');

--쿼리 내용
select sql_fulltext from v$sql where sql_id = '7cx84gahwk9sy';
select * from v$sql where sql_id = '7cx84gahwk9sy';

select p.spid, SID, s.SERIAL#, s.sql_id, s.USERNAME,COMMAND,LAST_CALL_ET AS SEC,
'alter system kill session '''
||SID||','||s.SERIAL#||''' immediate;' as tmp
FROM v$session s , v$process p
WHERE s.paddr=p.addr
--AND STATUS='ACTIVE'
AND STATUS='INACTIVE'
AND s.USERNAME IS NOT NULL
AND s.USERNAME != 'SYSTEM'
AND LAST_CALL_ET > 3000
order by sec desc;

***************
*락쿼리 조회
***************

SELECT DISTINCT T1.SESSION_ID
, T2.SERIAL#
, T4.OBJECT_NAME
, T2.MACHINE
, T2.TERMINAL
, T2.PROGRAM
, T3.ADDRESS
, T3.PIECE
, T3.SQL_TEXT
FROM V$LOCKED_OBJECT T1
, V$SESSION T2
, V$SQLTEXT T3
, DBA_OBJECTS T4
WHERE 1=1
AND T1.SESSION_ID = T2.SID
AND T1.OBJECT_ID = T4.OBJECT_ID
AND T2.SQL_ADDRESS = T3.ADDRESS
ORDER BY T3.ADDRESS, T3.PIECE
;

SELECT SQL_ID FROM V$SQL WHERE INSTR(SQL_FULLTEXT, 'FU_GET_RATE') > 0;

SELECT * FROM V$SESSION WHERE SQL_ID IN (SELECT SQL_ID FROM V$SQL WHERE INSTR(SQL_FULLTEXT, 'FU_GET_RATE') > 0);

ALTER SYSTEM KILL SESSION '100,200' IMMEDIATE;

-----------------------------------------------------------------------------------------------------------------------

1. 인스턴스(RAC경우), sid 추출

SQL> select inst_id, sid, owner, object, type from gv$access where object= 'PKG_EMP_STATS' ;

2. 해당 세션 확인
select sid, serial#, username, status
     , 'alter system kill session ''' || S.sid || ',' || S.serial# ||',@'||S.inst_id ||'''; ' as ask_se
 from gv$session S
where inst_id = '1'
  and sid = '2632' ;

3. 해당 세션 kill
alter system kill session '2632,23669,@1';

4. 세션 상태 확인
select sid, serial#, username, status
     , 'alter system kill session ''' || S.sid || ',' || S.serial# ||',@'||S.inst_id ||'''; ' as ask_se
 from gv$session S
where inst_id = '1'
  and sid = '2632' ;

5. Rollback 트랜젝션 확인

select S.inst_id, S.sid, T.status
  from gv$session S
     , gv$transaction T
where S.taddr = T.addr 
  and S.sid = '2632' ;