Oracle Open transactions ..
set linesize 300 pagesize 300
col USERNAME for a15
col object_name for a15
col owner for a12
col kill for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,t.start_scnw,t.start_scnb
,t.start_time -- <<<<--- format mm/dd/yy
,s.username, o.object_name,o.owner,t.used_ublk ,t.used_urec ,s.event,s.sql_id,s.prev_sql_id,s.status
from gv$transaction t, gv$session s, gv$locked_object l,dba_objects o
where 1=1
and t.ses_addr = s.saddr
and t.inst_id = s.inst_id
and t.xidusn = l.xidusn
and t.xidslot = l.xidslot
and t.xidsqn = l.xidsqn
and t.inst_id = l.inst_id
and l.object_id = o.object_id
-- and username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
-- l.object_id IN ('') ----< OBJECT ID FROM DBA_OBJECTS--and s.status='KILLED'
==
set linesize 300 pagesize 300
col kill for a15
col USERNAME for a24
col "Roll Status" for a27
col EVENT for a28
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.username,sql_id,t.used_urec,t.used_ublk,event
,case when bitand(flag,power(2,7)) > 0
then 'Rolling Back'
else 'Not Rolling Back'
end as "Roll Status"
from gv$session s, gv$transaction t
where 1=1
and s.saddr = t.ses_addr
and s.inst_id = t.inst_id
order by t.used_ublk desc
;
set linesize 300 pagesize 300
col kill for a15
col USERNAME for a24
col "Roll Status" for a27
col EVENT for a28
col CLIENT_INFO for a20
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,USERNAME, s.client_info, t.addr,sql_id, sum(t.used_ublk) used_ublk
,case when bitand(flag,power(2,7)) > 0
then 'Rolling Back'
else 'Not Rolling Back'
end as "Roll Status"
from gv$transaction t, gv$session s
where t.addr = s.taddr
and s.inst_id = t.inst_id
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',USERNAME, s.client_info, t.addr,sql_id,bitand(flag,power(2,7))
;
set linesize 300 pagesize 300
col kill for a15
col SQL_TEXT for a50 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, t.used_urec records, t.used_ublk blocks,(t.used_ublk*8192/1024) kb,sql.sql_text
from gv$transaction t,gv$session s, gv$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.inst_id = sql.inst_id
and s.inst_id = t.inst_id
and s.sql_id='&sql_id'
-- and s.username ='USERNAME'
;
set linesize 300 pagesize 300
col SQL_TEXT for a50 wrap
select distinct s.CON_ID,s.sql_id,s.sql_text from gv$sql s, gv$undostat u where u.maxqueryid=s.sql_id and s.inst_id = u.inst_id;
set lines 170
set pages 1000
col event format a35
select inst_id,event,count(*) from gv$session_wait
group by inst_id,event order by 3
/
col inst_id for 999
col sql format a35
col username format a20
col child format 999
col secs format 9999
col machine format a12
col event format a25
col state format a10
col MINS for 99999
select distinct
w.inst_id,w.sid,s.username,substr(w.event,1,25) event,s.type,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,--q.CHILD_NUMBER CHILD,
substr(q.sql_text,1,33) "SQL",round(s.LAST_CALL_ET/60) "MINS"
from gv$session_wait w,gv$session s,gv$sql q
where 1=1
and w.event like '%&event%'
and w.sid=s.sid
and w.inst_id=s.inst_id
and w.inst_id=q.inst_id
and s.SQL_HASH_VALUE=q.HASH_VALUE
and s.status='ACTIVE'
and s.username is not null
order by "MINS"
/
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col kill for a15
col username for a20
col sqlcommand for a20
col module for a20
col action for a20
col program for a20
col machine for a20
col client_info for a20
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
s.username,
s.machine,
NVL(s.client_info,'NA') client_info,
NVL(s.sql_id, s.prev_sql_id) sql_id,
NVL(sqlcom.command_name,'NA') sqlcommand,
s.module, s.action, s.program, t.status, t.start_date, ROUND((SYSDATE-t.start_date)*24*60*60) AS secondsopen,
SUM(t.used_urec) as "undo records used",
SUM(t.used_ublk) as "undo blocks used"
from gv$transaction t, gv$session s, gv$sqlcommand sqlcom
where 1=1
and t.addr = s.taddr
and sqlcom.command_type=s.command
and t.inst_id = s.inst_id
--and rownum <10
GROUP BY ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',
s.username, s.machine, s.client_info, NVL(s.sql_id, s.prev_sql_id), NVL(sqlcom.command_name,'NA') , s.module, s.action, s.program, t.status, t.start_date, (sysdate-t.start_date)*24*60*60
order by "undo blocks used" desc;
select count(f.block#) "Should Increase >", count(u.block#) "Should Decrease <" from fet$ f ,uet$ u;
===========
from Web
DEFINE BYTES_DIVIDER="1024/1024"
DEFINE BYTES_HEADING="MB"
DEFINE BYTES_FORMAT="999,999,999"
COLUMN xid HEADING "XID" FORMAT a16
COLUMN transaction_status HEADING "Tran|Status" FORMAT a8
COLUMN transaction_start_date HEADING "Tran|StartDate" FORMAT a18
COLUMN tran_duration HEADING "Tran|Duration" FORMAT a15
COLUMN space HEADING "Space|Tran" FORMAT a5
COLUMN recursive HEADING "Recu|rsive|Tran" FORMAT a5
COLUMN noundo HEADING "No|Undo|Tran" FORMAT a4
COLUMN ptx HEADING "Par'l|Tran" FORMAT a5
COLUMN used_undo HEADING "Undo|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
COLUMN log_io HEADING "Logical|IO" FORMAT 999,999,999
COLUMN phy_io HEADING "Physical|IO" FORMAT 999,999,999
COLUMN cr_get HEADING "Consistent|Gets" FORMAT 999,999,999
COLUMN name HEADING "Tran Name" FORMAT a15 WRAP
set heading off
select
'Session id : ' || s.sid || chr(10)
|| 'USERNAME : ' || s.USERNAME || chr(10)
|| 'Transaction Name : ' || TRIM(t.name) || chr(10)
|| 'XID : ' || TRIM(t.xid) || chr(10)
|| 'Parent XID : ' || TRIM(t.ptx_xid) || chr(10)
|| 'Tran Status : ' || TRIM(t.status) || chr(10)
|| 'Tran Start Time : ' || TO_CHAR(t.start_date,'DD-MON-YY HH24:MI:SS') || chr(10)
|| 'Tran Duration : ' || FLOOR(sysdate - t.start_date) || 'd '
|| LPAD(FLOOR(MOD((sysdate - t.start_date) , 1) * 24 ) ,2) || 'h '
|| LPAD(FLOOR(MOD((sysdate - t.start_date) * 24 , 1) * 60 ) ,2) || 'm '
|| LPAD(FLOOR(MOD((sysdate - t.start_date) * 24 * 60 , 1) * 60 ) ,2) || 's ' || chr(10)
|| 'Parallel Tran : ' || TRIM(t.ptx) || chr(10)
|| 'Space Tran : ' || TRIM(t.space) || chr(10)
|| 'Recursive Tran : ' || TRIM(t.recursive) || chr(10)
|| 'No UNDO Tran : ' || TRIM(t.noundo) || chr(10)
|| 'Undo : ' || TRIM(TO_CHAR(ROUND((t.used_ublk * p.value)/&&BYTES_DIVIDER),'&&BYTES_FORMAT')) || ' &&BYTES_HEADING' || chr(10)
|| 'Logical IO : ' || TRIM(TO_CHAR(t.log_io,'999,999,999')) || chr(10)
|| 'Physical IO : ' || TRIM(TO_CHAR(t.phy_io,'999,999,999')) || chr(10)
|| 'Consistent Gets : ' || TRIM(TO_CHAR(t.cr_get,'999,999,999')) || chr(10)
|| 'SQl id : ' || s.sql_id || chr(10)
|| 'PREV SQL ID : ' || PREV_SQL_ID || chr(10)
||'EVENT : ' || EVENT || chr(10)
||'alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' || chr(10)
FROM gv$transaction t
INNER JOIN gv$session s ON t.inst_id = s.inst_id AND t.ses_addr = s.saddr
INNER JOIN v$parameter p ON p.name = 'db_block_size'
WHERE 1=1
--and s.inst_id = :INST_ID
-- AND s.sid = :SID
ORDER BY t.start_date
;
===
From Web
set linesize 500
col username for a30
col osuser for a14
col module for a20
col program for a30
col status for a12
col name for a5
col start_time for a18
col EVENT for a25
col kill for a16
with v as (
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' as kill,
--s.sid,
s.username
,s.osuser
,s.module,s.program
,s.sql_id
,s.event
--,addr,ses_addr
,tr.xid
,tr.xidusn
,tr.xidslot
,tr.xidsqn
--,ubafil,ubablk,ubasqn,ubarec
,tr.status
,tr.start_time
--,tr.start_date
,tr.used_ublk
,tr.used_urec
,tr.log_io
,tr.phy_io
,tr.cr_get
,tr.cr_change
,tr.flag
,tr.space
,tr.recursive
,tr.noundo
,tr.ptx parallel_tx
,tr.name
,tr.start_scn
--,dependent_scn
--,start_scnb,start_scnw,start_uext,start_ubafil,start_ubablk,start_ubasqn,start_ubarec
--,prv_xidusn,prv_xidslt,prv_xidsqn
--,ptx_xidusn,ptx_xidslt,ptx_xidsqn
--,dscn-b,dscn-w
--,dscn_base,dscn_wrap
--,prv_xid,ptx_xid
,row_number()over(order by used_ublk desc) blks_rn
,row_number()over(order by used_urec desc) recs_rn
,row_number()over(order by start_time desc) time_rn
from gv$transaction tr
,gv$session s
where 1=1
and tr.ses_addr = s.saddr(+)
and tr.inst_id = s.inst_id
)
select
kill
,sql_id
,event
,username
,osuser
,substr(module,1,30) as module
,program
--,xid
--,xidusn,xidslot,xidsqn
,status
,start_time
,used_ublk
,used_urec
,log_io
,phy_io
,cr_get
,cr_change
--,flag
,space,recursive,noundo,parallel_tx
,name
,start_scn
from v
where blks_rn<=10
or recs_rn<=10
or time_rn<=10
order by blks_rn
/
col username clear
col osuser clear
col module clear
col program clear
col status clear
col name clear
col start_time clear
