Get Information on Current Queries (10g)

From Relyimah

This script provides information about the queries that are currently using temporary segments. (This script runs on Oracle Database 10g.)

col tablespace FOR a8
col username FOR a10
col STATUS FOR a8
col program FOR a15
col sql_text FOR a30
col blocks FOR 9999999
SET linesize 350
SET pagesize 300
 
ALTER session SET nls_date_format='DD/MM/YYYY HHAM:MI:SS';
 
SELECT t.tablespace_name AS tablespace,us.username,s.STATUS,s.sid,s.serial#,s.program,sql.sql_text,us.blocks,s.logon_time
FROM v$tempseg_usage us, v$session s, v$sqlarea sql, dba_tablespaces t
WHERE t.contents = 'TEMPORARY' AND
us.tablespace=t.tablespace_name AND
us.session_addr = s.saddr AND
s.sql_address = sql.address
ORDER BY blocks
/

Source: Oracle Technology Network