ORA-01652: Who is using the temp tablespace?

if you are getting the error “ORA-01652: unable to extend temp segment by 128 in tablespace TEMP” you could just increase the temp tablespace, i.e. by adding an additional tempfile… the smarter way would be to check who is consuming so much temp tablespace…

The following SQL is giving you a live view:

SELECT
	 s.sid
	,s.serial#  
	,s.osuser
	,s.user#
	,s.username 
	,s.sql_id
	,s.module
	,s.program 
	,round(su.blocks * tbs.block_size / 1024 / 1024 /1024) gb_used 
	,su.tablespace
	--,sa.sql_text     -- uncomment if you want to see the SQL text
	--,sa.sql_fulltext -- uncomment if your Client can handle CLOB's (i.e. SQL Developer)
FROM  v$sort_usage su   
inner join v$session s on su.session_addr=s.saddr
inner join dba_tablespaces tbs on su.tablespace=tbs.tablespace_name
inner join v$sqlarea sa on s.sql_id=sa.sql_id
where su.blocks*tbs.block_size > 1024*1024*1024 -- more than 1 GB TEMP
ORDER BY gb_used desc; 

and if you have to check what happende some time ago (take care: Diagnosting & Tuning pack required!)

SELECT
   snap_id
  ,sample_time
  ,session_id
  ,session_serial#
  ,sql_id
  ,round(temp_space_allocated/1024/1024/1024) gb_used
  FROM dba_hist_active_sess_history
WHERE temp_space_allocated > 1024*1024*1024
ORDER BY sample_time;