Shows current transaction data, including start time, run time, undo records used blocks, program etc
SQL> @trans
Bug 3060261 can cause 'Start Time' to be ahead of sysdate
SYSDATE
-------------------
02/10/2015 14:58:04
INST_ID Alert SID User CLIENT_INFO Logon Time Ses Stat Tran Stat Start Time Minutes Undo Recs Used Blk Program Logical IO Phys IO Cons Gets Cons Changes Spa Rec No Par Transaction OSUSER
---------- ------- ----- -------------------- ------------ ------------------- -------- ---------- -------------------- ----------- ------------ ---------- ------------------------------ ---------- ---------- ---------- ------------ --- --- --- --- ------------------------------ ----------
1 764 XYZ_CONN 02/10/2015 14:44:01 ACTIVE ACTIVE 10/02/15 14:44:00 14.07 14,283,728 251204 monitor@linuxuts (TNS V1-V3) 69030651 216763 352508 202 NO NO NO NO oracle
1 WARNING 442 SHARED_CONN1 u00001 02/10/2015 09:20:04 INACTIVE ACTIVE 10/02/15 12:07:54 170.17 3 1 sqlplus@linux900 (TNS V1-V3) 124 9 673 0 NO NO NO NO oracle
1 WARNING 124 SHARED_CONN1 u000002 02/10/2015 08:59:02 INACTIVE ACTIVE 10/02/15 13:36:09 81.92 2 1 sqlplus@linux900 (TNS V1-V3) 88 113 1125 0 NO NO NO NO oracle
1 WARNING 99 SHARED_CONN1 u000002 02/10/2015 13:37:46 INACTIVE ACTIVE 10/02/15 13:38:11 79.88 2 1 sqlplus@linux900 (TNS V1-V3) 928 11165 50607 2 NO NO NO NO oracle
1 382 SHARED_CONN1 u000005 02/10/2015 10:52:51 INACTIVE ACTIVE 10/02/15 14:29:17 28.78 2 1 sqlplus@linux900 (TNS V1-V3) 90 218 1941 0 NO NO NO NO oracle
1 842 SHARED_CONN1 u00001 02/10/2015 09:14:13 INACTIVE ACTIVE 10/02/15 14:37:39 20.42 2 1 sqlplus@linux900 (TNS V1-V3) 74 7 393 0 NO NO NO NO oracle
1 813 SHARED_CONN1 u000008 02/10/2015 09:18:44 INACTIVE ACTIVE 10/02/15 14:49:15 8.82 2 1 sqlplus@linux900 (TNS V1-V3) 72 5 379 0 NO NO NO NO oracle
1 661 SHARED_CONN1 u000010 02/10/2015 10:32:32 INACTIVE ACTIVE 10/02/15 14:49:34 8.50 2 1 sqlplus@linux900 (TNS V1-V3) 78 13 656 0 NO NO NO NO oracle
1 635 SHARED_CONN1 u000010 02/10/2015 09:44:39 INACTIVE ACTIVE 10/02/15 14:50:58 7.10 2 1 sqlplus@linux900 (TNS V1-V3) 239 624 5406 1 NO NO NO NO oracle
1 258 SHARED_CONN1 super01 02/10/2015 09:02:14 INACTIVE ACTIVE 10/02/15 14:50:59 7.08 2 1 sqlplus@linux900 (TNS V1-V3) 177 1362 4173 0 NO NO NO NO oracle
1 316 SHARED_CONN1 u000002 02/10/2015 14:18:37 INACTIVE ACTIVE 10/02/15 14:57:48 .27 2 1 sqlplus@linux900 (TNS V1-V3) 98 166 1000 0 NO NO NO NO oracle
1 893 SHARED_CONN1 u00001 02/10/2015 08:29:40 INACTIVE ACTIVE 10/02/15 14:58:04 .00 2 1 sqlplus@linux900 (TNS V1-V3) 80 31 698 0 NO NO NO NO oracle
1 603 SHARED_005 02/10/2015 14:52:40 ACTIVE ACTIVE 10/02/15 14:58:03 .02 6 1 JDBC Thin Client 276 267 3045 0 NO NO NO NO wasadmin
1 593 SHARED_005 02/10/2015 14:51:13 ACTIVE ACTIVE 10/02/15 14:58:03 .02 1 1 JDBC Thin Client 227 123 1613 0 NO NO NO NO wasadmin
1 40 SHARED_005 02/10/2015 14:53:01 ACTIVE ACTIVE 10/02/15 14:58:04 .00 1 1 JDBC Thin Client 22 2 21 0 NO NO NO NO wasadmin
1 560 SHARED_005 02/10/2015 14:35:49 ACTIVE ACTIVE 10/02/15 14:58:04 .00 1 1 JDBC Thin Client 25 9 123 0 NO NO NO NO wasadmin
1 312 SHARED_005 02/10/2015 14:54:41 ACTIVE ACTIVE 10/02/15 14:58:04 .00 4 1 JDBC Thin Client 287 85 2297 0 NO NO NO NO wasadmin
1 192 SHARED_005 02/10/2015 14:51:13 ACTIVE ACTIVE 10/02/15 14:58:04 .00 1 1 JDBC Thin Client 317 118 1564 0 NO NO NO NO wasadmin
18 rows selected.
col "User" for a20
col "SID" for 9999
col "Tran Stat" for a10
col "Minutes" for 999,999.99
col "Transaction" for a30
col client_info for a12
col "Undo Recs" for 999,999,999
set feed off
spool trans.rpt
select sysdate from dual;
set feed on
rem set Alert to 'Warning' if transaction started over 30 minutes ago
select s.inst_id,
decode( sign( 30 - ( sysdate - to_date( t.start_time, 'mm/dd/rr hh24:mi:ss' ) ) * 24 * 60 ), -1, 'WARNING', null ) "Alert",
s.sid "SID",
s.username "User", client_info,
s.logon_time "Logon Time",
s.status "Ses Stat",
t.status "Tran Stat",
t.start_time "Start Time",
( sysdate - to_date( t.start_time, 'mm/dd/rr hh24:mi:ss' ) ) * 24 * 60 "Minutes",
t.used_urec "Undo Recs",
t.used_ublk "Used Blk",
s.program "Program",
t.log_io "Logical IO",
t.phy_io "Phys IO",
t.cr_get "Cons Gets",
t.cr_change "Cons Changes",
t.space "Space",
t.recursive "Recursive",
t.NoUndo "No Undo",
t.ptx "Parallel",
t.name "Transaction",
s.osuser
from gv$transaction t,
gv$session s
where t.inst_id = s.inst_id
and t.addr = s.taddr
order by s.username, t.start_time
/
spool off