반응형

/************************
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
;

 

 

 

반응형