/**********************************************************************
 * File:	ptx.sql
 * Type:	SQL*Plus script
 * Author:	Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:	15-May-99
 *
 * Description:
 *	Queries the V$TRANSACTION view for "parallel DML" commands in
 *	an Oracle8 database.  Displays "progress" of the operation in
 *	terms of "logical I/O" (i.e. buffer gets/writes) and "physical
 *	I/O" (i.e. disk accesses)...
 *
 * Modifications:
 *********************************************************************/
col sid format 990 heading "Sid"
col username format a8 heading "Username"
col program format a15 word_wrap heading "Program[Status]"
col io format a16 word_wrap heading "Log/Phy IO"
col run_tm format a8 word_wrap heading "Run Tm"
col event format a25 word_wrap heading "Wait Event"

set linesize 500 trimspool on echo off feedback off timing off


select	s.sid,
	s.username,
	s.program || ' [' || t.status || ']' program,
	to_char(t.log_io) || '/' || to_char(t.phy_io) io,
	round((sysdate - to_date(t.start_time, 'MM/DD/YY HH24:MI:SS'))
		* 1440, 0) || ':' ||
	ltrim(to_char(round(mod((sysdate - to_date(t.start_time,
				    'MM/DD/YY HH24:MI:SS'))
		* 86400, 60), 0),'00')) run_tm,
	w.event || ' (' || p1text || '=' || p1 || ', ' ||
			  p2text || '=' || p2 || ', ' ||
			  p3text || '=' || p3 || ')' event
from	v$session	s,
	v$transaction	t,
	v$session_wait	w
where	t.ptx = 'YES'
and	s.taddr = t.addr
and	w.sid (+) = s.sid

spool ptx
/
spool off