user transuction details

SQL> set timing on
SQL> SET HEADING ONprompt ******************* User Transactions Information ****************************************
SQL> column "UserName" format a8
SQL> column "DB Sid" format 999999
SQL> column "Unix Pid" format 99999999
SQL> column "Trnx_start_time" format a19
SQL> column "Current Time" format a19
SQL> column "Elapsed(mins)" format 999999999.99
SQL> column "Undo Name" format a09
SQL> column "Used Undo Blks" format a13
SQL> column "Used Undo Size(Kb)" format a17
SQL> column "Logical I/O(Blks)" format 99999999999999999
SQL> column "Logical I/O(Kb)" format 999999999999999
SQL> column "Physical I/O(Blks)" format 999999999999999999
SQL> column "Physical I/O(Kb)" format 999999999999999999
SQL> select a.username "UserName" ,
2 a.sid "DB Sid",
3 e.spid "Unix Pid",
4 to_char(to_date(b.start_time,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') "Trnx_start_time",
5 to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Current Time",
6 round(60*24*(sysdate-to_date(b.start_time,'mm/dd/yy hh24:mi:ss')),2) "Elapsed(mins)",
7 c.segment_name "Undo Name",
8 to_char(b.used_ublk*d.value/1024) "Used Undo Size(Kb)",
9 to_char(b.used_ublk) "Used Undo Blks",
10 b.log_io "Logical I/O(Blks)" ,
11 b.log_io*d.value/1024 "Logical I/O(Kb)",
12 b.phy_io "Physical I/O(Blks)",
13 b.phy_io*d.value/1024 "Physical I/O(Kb)",
14 a.PROGRAM
15 from v$session a,
16 v$transaction b,
17 dba_rollback_segs c,
18 v$parameter d,
19 V$PROCESS e
20 where b.ses_addr=a.saddr
21 and b.xidusn=c.segment_id
22 and d.name='db_block_size'
23 and e.ADDR=a.PADDR
24 order by 4;

UserName DB Sid Unix Pid Trnx_start_time Current Time Elapsed(mins) Undo Name Used Undo Size(Kb Used Undo Blk Logical I/O(Blks) Logical I/O(Kb) Physical I/O(Blks) Physical I/O(Kb) PROGRAM
-------- ------ -------- ------------------- ------------------- ------------- --------- ----------------- ------------- ----------------- --------------- ------------------ ------------------ ------------------------------------------------
APPS 316 15520 2009/11/14 11:04:08 2009/11/14 11:04:24 0.27 _SYSSMU18 8 1 3 24 0 0 FNDSCH@kmctapp2 (TNS V1-V3)
$


Executed in 0.032 seconds

SQL>