Oracle Open transactions Info ..

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