A temporary tablespace can run out of space or become full and we may get error ORA-1652.

ORA-1652 "unable to extend temp segment by %s in tablespace %s"

This post provides the queries to find out the SQL which is using these temp segments.

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

This query will not assist in determination of the amount of space consumed in a temporary tablespace. If this is desired, then other queries on v$sort_usage or v$tempseg_usage should be used.

Example of how to monitor Temporary Segment Usage over time

1. Create a table to hold your temporary space monitoring:

CREATE TABLE [temporary tablespace name to monitor]_TEMP_SEG_USAGE(
  DATE_TIME  DATE,
  USERNAME VARCHAR2(30),
  SID                  VARCHAR2(6),
  SERIAL#        VARCHAR2(6),
  OS_USER     VARCHAR2(30),
  SPACE_USED NUMBER,
  SQL_TEXT    VARCHAR2(1000));

2. Create a job in the job queue to insert into your monitoring table:

CREATE OR REPLACE PROCEDURE [temporary tablespace name to monitor]_TEMP_SEG_USAGE_INSERT IS
BEGIN
  insert into [temporary tablespace name to monitor]_TEMP_SEG_USAGE
    SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
    FROM v$session a, v$tempseg_usage b, v$sqlarea c
    WHERE b.tablespace = [temporary tablespace name to monitor]
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND b.blocks*[block size of the temp tablesapace] > [threshold]
    ORDER BY b.tablespace, b.blocks;
COMMIT;
END;
/

BEGIN
  DBMS_JOB.ISUBMIT(
    JOB => [A job # not in use in the DBA_JOBS],
    WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;'
    NEXT_DATE => SYSDATE,
    INTERVAL => 'SYSDATE + ([NUMBER OF MINUTES BETWEEN SAMPLES]/1440)'
                                          );
COMMIT;
END;
/

For 12.2 and above, please use scheduler job as dbms_jobs are deprecated in 12.2 and above.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => '[your job name]',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN [your procedure name]; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => '[your job's execution internal]',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job created to monitor temp usage.');
END;
/

3. Periodically query your monitoring table [temporary tablespace name to monitor]_TEMP_SEG_USAGE.

sql> select * from [temporary tablespace name to monitor]_TEMP_SEG_USAGE;

4. Also monitor space usage of the table as it could grow very fast depending on job interval; delete rows or truncate table as appropriate.

sql> select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb from dba_segments
where segment_name="[temporary tablespace name to monitor]_TEMP_SEG_USAGE;";
sql> truncate table [temporary tablespace name to monitor]_TEMP_SEG_USAGE;