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_db_sorters.sql
REM
REM <SQLDIR_GRP>STATS</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports sessions that with active sorts
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_db_sorters.sql
REM 
REM Example:
REM	s_db_sorters.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

prompt
prompt Here are All sorts in the database
prompt Make sure SORTs are not in SYSTEM tablespace
prompt

set pages 900
col tablespace format a9
col file_name format a30
col terminal format a15
col username format a12
col kb format 999999 heading "KB"
col sid_ser format a10
col sid format 999
break on sid skip 1 on serial# on terminal on report

--compute sum of mb on file_name
compute sum of mb on sid_ser
compute sum of mb on report

set lines 132

select  SUBSTR(s.username,1,12) username
,       s.sid||','||s.serial# sid_ser
,       s.terminal
,       s.status
,       u.tablespace
,       DECODE(dbf.file_name,NULL,tdbf.file_name) file_name
,       u.contents
,       u.extents
,       u.blocks * p.value/1024 kb
from    v$parameter  p
,       v$session    s
,       dba_data_Files dbf
,       dba_temp_Files tdbf
,       v$sort_usage u
where   s.saddr = u.session_addr
and     (u.segfile# = dbf.file_id    OR
         u.segfile# = tdbf.file_id)
--and     u.contents = 'TEMPORARY'
and     p.name = 'db_block_size'
order by s.sid, dbf.file_name
/