Oracle Transaction Monitor

accept x_revolutions prompt "No of cycles: "
accept x_sleep_secs prompt "Seconds for sleep interval: "
prompt

declare

cursor c_trans_sess is
select a.addr,
a.status "TStatus",
a.start_time,
a.name,
a.used_ublk,
a.used_urec,
a.log_io,
a.phy_io,
b.sid,
b.serial#,
b.username,
b.taddr,
b.status "SStatus",
b.osuser,
b.program,
b.client_info,
b.logon_time
from v$transaction a,
v$session b
where b.taddr = a.addr
order by b.sid;

type tr_trans_sess is record
( t_addr v$transaction.addr%type,
t_status v$transaction.status%type,
t_start_time v$transaction.start_time%type,
t_name v$transaction.name%type,
t_used_ublk v$transaction.used_ublk%type,
t_used_urec v$transaction.used_urec%type,
t_log_io v$transaction.log_io%type,
t_phy_io v$transaction.phy_io%type,
s_sid v$session.sid%type,
s_serial# v$session.serial#%type,
s_username v$session.username%type,
s_taddr v$session.taddr%type,
s_status v$session.status%type,
s_osuser v$session.osuser%type,
s_program v$session.program%type,
s_client_info v$session.client_info%type,
s_logon_time v$session.logon_time%type
);
type tt_trans_sess is table of tr_trans_sess index by binary_integer;

lt_trans_sess tt_trans_sess;

ln_counter number := 0;

ln_rec_loc number := -1;
ls_sql varchar2( 2000 );
ls_head1 varchar2( 2000 );
ls_head2 varchar2( 2000 );

ln_revolutions number := to_number( nvl( '&x_revolutions', '1' ) );
ln_sleep_secs number := to_number( nvl( '&x_sleep_secs', '5' ) );

begin
for i in c_trans_sess loop
ln_counter := ln_counter + 1;

lt_trans_sess( ln_counter ).t_addr := i.addr;
lt_trans_sess( ln_counter ).t_status := i."TStatus";
lt_trans_sess( ln_counter ).t_start_time := i.start_time;
lt_trans_sess( ln_counter ).t_name := i.name;
lt_trans_sess( ln_counter ).t_used_ublk := i.used_ublk;
lt_trans_sess( ln_counter ).t_used_urec := i.used_urec;
lt_trans_sess( ln_counter ).t_log_io := i.log_io;
lt_trans_sess( ln_counter ).t_phy_io := i.phy_io;
lt_trans_sess( ln_counter ).s_sid := i.sid;
lt_trans_sess( ln_counter ).s_serial# := i.serial#;
lt_trans_sess( ln_counter ).s_username := i.username;
lt_trans_sess( ln_counter ).s_taddr := i.taddr;
lt_trans_sess( ln_counter ).s_status := i."SStatus";
lt_trans_sess( ln_counter ).s_osuser := i.osuser;
lt_trans_sess( ln_counter ).s_program := i.program;
lt_trans_sess( ln_counter ).s_client_info := i.client_info;
lt_trans_sess( ln_counter ).s_logon_time := i.logon_time;

end loop;

for cycles in 1..ln_revolutions loop
dbms_lock.sleep( ln_sleep_secs );

dbms_output.put_line( '' );
dbms_output.put_line( 'Cycle: ' || cycles );
dbms_output.put_line( '' );

ls_head1 := rpad( 'Username', 30 ) || ' ' ||
lpad( 'SID', 5 ) || ' ' ||
rpad( 'Usr Status', 10 ) || ' ' ||
rpad( 'Tran Stat', 10 ) || ' ' ||
'Used Blocks1' || ' ' ||
'Used Blocks2' || ' ' ||
' Used Recs1' || ' ' ||
' Used Recs2' || ' ' ||
' Rec Change' || ' ' ||
rpad( 'Tran Start', 20 );
ls_head2 := rpad( '--------', 30 ) || ' ' ||
lpad( '---', 5 ) || ' ' ||
rpad( '----------', 10 ) || ' ' ||
rpad( '---------', 10 ) || ' ' ||
'------------' || ' ' ||
'------------' || ' ' ||
' ----------' || ' ' ||
' ----------' || ' ' ||
' ----------' || ' ' ||
rpad( '----------', 20 );

dbms_output.put_line( ls_head1 );
dbms_output.put_line( ls_head2 );

for i in c_trans_sess loop
ln_rec_loc := -1;
-- locate the transaction
for j in 1..ln_counter loop
if lt_trans_sess( j ).t_addr = i.addr then
ln_rec_loc := j;
exit;
end if;
end loop;
if ln_rec_loc > 0 then
ls_sql := rpad( nvl( lt_trans_sess( ln_rec_loc ).s_username, ' '), 30 ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).s_sid, '9999' ) || ' ' ||
rpad( lt_trans_sess( ln_rec_loc ).s_status, 10 ) || ' ' ||
rpad( lt_trans_sess( ln_rec_loc ).t_status, 10 ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).t_used_ublk, '999,999,999' ) || ' ' ||
to_char( i.used_ublk, '999,999,999' ) || ' ' ||
to_char( lt_trans_sess( ln_rec_loc ).t_used_urec, '999,999,999' ) || ' ' ||
to_char( i.used_urec, '999,999,999' ) || ' ' ||
to_char( i.used_urec - lt_trans_sess( ln_rec_loc ).t_used_urec, '999,999,999' ) || ' ' ||
lt_trans_sess( ln_rec_loc ).t_start_time;
if lt_trans_sess( ln_rec_loc ).t_used_urec > i.used_urec then
ls_sql := ls_sql || ' ' || 'Rolling back?';
end if;
else
ls_sql := rpad( nvl( i.username, ' ' ), 30 ) || ' ' ||
to_char( i.sid, '9999' ) || ' ' ||
rpad( i."SStatus", 10 ) || ' ' ||
rpad( i."TStatus", 10 ) || ' ' ||
rpad( ' ', 12 ) || ' ' ||
to_char( i.used_ublk, '999,999,999' ) || ' ' ||
rpad( ' ', 12 ) || ' ' ||
to_char( i.used_urec, '999,999,999' ) || ' ' ||
' ' || ' ' ||
i.start_time;

end if;
if ln_rec_loc > 0 then
dbms_output.put_line( ls_sql );
end if;
end loop;

end loop; -- end revolution cycles

end;
/