REM
/*
 * ShowTransactionStatus.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
SET feedback off pause off pagesize 22 linesize 132
ttitle center "TRANSACTION STATUS" skip 2
col START_TIME format a20 heading "Start Time"
col UNAM format a20        heading "User|Details"
col PROG format a30 word heading "Program|Client Terminal Details"
--Asterixes after username indicates current session
col NAME format a15 heading "Action"
col USED_UBLK format 9,999,999,999 heading "Rollback|Blocks"
col USED_UREC format 9,999,999,999 heading "Rollback|Records"
SELECT
--
 NVL(b.username,'ORACLE PROC')||' ('||b.sid||')'||DECODE(b.AUDSID,userenv('SESSIONID'),'**','') UNAM,
      b.program||'-'||b.terminal||'('||b.machine||') as '||b.osuser PROG,
--
TO_CHAR(to_date(t.START_TIME,'MM/DD/YY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') START_TIME,
 c.name,
 t.used_ublk,
 t.used_urec
FROM 	V$TRANSACTION t
	,V$SESSION b
	,sys.AUDIT_ACTIONS c
	,V$PROCESS d
WHERE 	t.SES_ADDR=b.SADDR
AND	b.COMMAND=c.ACTION
AND	d.addr=b.paddr
ORDER BY 1,2
/

ttitle off
set feedback on

Prompt
Prompt A session will only be listed if it is INSERTing, UPDATEing, DELETEing
Prompt or performing DDL (eg CREATE TABLE) or COMMITting, ROLLBACKing DML.
Prompt An action of UNKNOWN represents an uncommitted transaction.
Prompt The Rollback blocks/records indicate the progress of the transaction,
Prompt by monitoring these you can see how far a statement has got and how quickly
Prompt it is progressing (for DML they increase, for rollback they decrease down to 0).
Prompt NB Rollback records DOES NOT necessarily equate to rows processed (but it may do).
Prompt
Prompt Logical and physical IO can also be shown as can SCN via other V$TRANSACTION columns.
Prompt

SET DOC off
/* SUMMARY - Indicate progress of a transaction by displaying the number of blocks and rows that have been changed.
 *
 * DETAIL - this script uses the Oracle V$Transaction table to show you how much work has currently been done in active transactions
 * (as blocks and rows). It will show all users with uncommited or non-rolledback transactions, when the transaction staterd, what 
 * action the session is currently undertaking (NB this is not necessarily the action that caused the transaction) and the
 * number of blocks/rows affected.
 *
 * This is clearly useful to show who on the system is doing work but it has two other uses. Firstly, if you run the script
 * a few times and the number of blocks/records is increasing then the session is continuing within the transaction as normal. If the 
 * number is going down then the transaction is being rolled back. If the number remains the same then either it is waiting for a commit
 * or the session is waiting on some resource to continue (which may be waiting on a lock or simply waiting on more data to be made
 * available to add to the transaction - you see this quite often when there is some other SQL driving the transaction and it has a 
 * lot of work to do to get data, prehaps from a full table scan).
 * 
 * Secondly, if you know what the transaction is and what indexes are involved you can get an idea of how much work has been done,
 * and if you know how many rows should be affected this can tell you how far the transaction has progressed. The number given is 
 * for table records and index entries. So the number of rollback records shown equates, roughly, to 1 for each row
 * inserted/updated/deleted and 1 for each index entry affected.
 * 
 * Please note, however, that a transaction does not imply a single DML operation - a transaction can be made up of any number of
 * uncommited operations. The number of rollback records applies to all DML operations and hence potentially multiple tables. In addition,
 * DML from with triggers is also included.
 *          
 * (c) Tim Onions 2007
 * Script used at the database owners risk.
 */

REM