REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_user_redos.sql
REM
REM <SQLDIR_GRP>LOG USER TRACE</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports sessions with highest redo sizes
REM     takes total redo size as denominator
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	s_user_redos.sql
REM 
REM Example:
REM	s_user_redos.sql
REM
REM
REM History:
REM	12-03-2001	VMOGILEV	Created
REM
REM

set lines 132
ttit off
btit off


spool s_user_redos.log
SELECT sid, ABS(value),total.val total_redo,((ABS(value) * 100) / total.val) pct
FROM v$sesstat
,    (select sum(ABS(value)) val
      FROM v$sesstat
      WHERE statistic# in (select statistic# from v$statname where name like 'redo size')) total
WHERE statistic# in (select statistic# from v$statname where name like 'redo size')
AND   ((ABS(value) * 100) / total.val) > .01
ORDER BY ABS(value) desc
/

select * From v$sysstat where name like '%redo%';

select * From v$statname where name like '%redo%';

spool off