8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: user_temp_space.sql
( Download Script )
-- ----------------------------------------------------------------------------------- -- File Name : http://www.oracle-base.com/dba/monitoring/user_temp_space.sql -- Author : DR Timothy S Hall -- Description : Displays the temp space currently in use by users. -- Requirements : Access to the V$ views. -- Call Syntax : @user_temp_space -- Last Modified: 12/02/2004 -- ----------------------------------------------------------------------------------- COLUMN tablespace FORMAT A20 COLUMN temp_size FORMAT A20 COLUMN sid_serial FORMAT A20 COLUMN username FORMAT A20 COLUMN program FORMAT A40 SET LINESIZE 200 SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, a.sid||','||a.serial# AS sid_serial, NVL(a.username, '(oracle)') AS username, a.program FROM v$session a, v$sort_usage b, v$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks;