Who use Temporary Tablespace ?

Temporary Tablespace is used for sorting purpose it also temporarily data like temporary tables.

Here is a query that give you, which sql statement use who much on Temporary Tablespace (I considered TEMP is a 8k block size tablespace)

select su.username
, dbms_lob.substr(sq.sql_fulltext, 4000, 1) sql_text
, su.blocks
, su.extents
, su.segtype
, (su.blocks*8)/1024 size_MB
, sq.last_active_time
, sq.last_load_time
from v$sort_usage su
join v$sqlarea sq on (su.sql_id = sq.sql_id)
left join v$session s on (s.sql_id=su.sql_id)

where su.tablespace='TEMP';

ORA-10631

Message: ORA-10631: SHRINK clause should not be specified for this object

Cause: shrink clause use inappropriately.

Solution: Write the syntax correctly. If a table has function based index or Text index, it can not be shrinked