Which user is using the Temporary Tablespace

Some times it is necessary to know which users are using the temporary tablespace. As some times the temporary tablespace might get filled up and if autoextend on, than it can make the file system fill, which can create a severe problem for a DBA.

We can check from SQL Plus which users are using the temporary tablespace.

Here is the query which can be used.............

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)'M' "SIZE", a.sid','a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks
/