who is using my temp tablespace

SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

http://www.dbspecialists.com/files/presentations/temp_space.html

Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about
temporary tablespace usage.


SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------- ---------------             --------------                 ----------
TEMP
                                                        134217728 
                   134217728                    133169152

With this information, you can perform an online shrink of a temporary tablespace
using the ALTER TABLESPACE command.

SQL> alter tablespace temp shrink space keep 100m;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------------------    ---------------                  ---------------           ------------
TEMP                           
                              105906176                           
1048576              104857600

The shrink can also be directed to a specific tempfile using the TEMPFILE clause.

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/grdbms/32bit/app/oracle/oradata/S111W3/temp01.dbf' KEEP 30M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------        ---------------              ---------------            ------------
TEMP                               
                               31522816                           
1040384                    31457280

1 row selected.

The
KEEP clause specifies the minimum size of the tablespace or tempfile. If
this is omitted, the database will shrink the tablespace or tempfile to
the smallest possible size.

SQL> ALTER TABLESPACE temp SHRINK SPACE;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------        ---------------           ---------------            -------------
TEMP                                  2088960                        1040384                     1048576
set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr