ORA-01652 : unable to extend temp segment by 128 in tablespace TEMP

From the application perspective, this error can occur in any part of the application, depending on the database operation required.

Please refer to RSA Knowledge Base Article 000030327 Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the log files for your specific deployment. 

Determine the status of TEMP tablespace 

1. Login as the oracle user and connect to the database as user SYS using the command below or by using SQL Developer. 

sqlplus "/as sysdba"

2. Run the queries below to determine current TEMP tablespace sizing and free space:
   1. This query lists the size of each file segment in MB: 

SELECT   b.TABLESPACE, b.segfile# , b.segblk#,
         ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) SIZE_in_MB,
         a.SID , a.serial#  , a.username,
         a.osuser , a.program , a.status
FROM     v$session a , v$sort_usage b , v$process c , v$parameter p
WHERE    p.NAME = 'db_block_size'
AND      a.saddr = b.session_addr
AND      a.paddr = c.addr
ORDER BY b.TABLESPACE,
         b.segfile# , b.segblk# , b.blocks;

2. This query lists the overall size in total and how much is being used in MB:

SELECT   d.tablespace_name,
         NVL(a.bytes / 1024 / 1024, 0) Total_in_MB ,
         NVL(t.bytes, 0)/1024/1024 Used_in_MB
FROM     sys.dba_tablespaces d,
         (SELECT tablespace_name, SUM(bytes) bytes, COUNT(file_id) COUNT
         FROM dba_temp_files
         GROUP BY tablespace_name) a,
         (SELECT ss.tablespace_name ,
         SUM((ss.used_blocks*ts.blocksize)) bytes
         FROM gv$sort_segment ss, sys.ts$ ts
         WHERE ss.tablespace_name = ts.name
         GROUP BY ss.tablespace_name) t
WHERE    d.tablespace_name = a.tablespace_name(+)
AND      d.tablespace_name   = t.tablespace_name(+)
AND      d.tablespace_name LIKE 'TEMP%';

3. This query gives the number of free_blocks still available in TEMP tablespace:​

SELECT sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

4. This query gives also lists the free space in TEMP tablespace in MB:

SELECT tablespace_name, total_blocks, used_blocks, free_blocks,   
       (total_blocks*8)/1024 as total_MB,   
       (used_blocks*8)/1024 as used_MB,   
       (free_blocks*8)/1024 as free_MB 
FROM   v$sort_segment;

3. To find the specific SQL query or queries that are using lots of TEMP tablespace in Oracle:

1. Execute the following SQL:

col hash_value for a40;
col tablespace for a10;
col username for a15;
set linesize 132 pagesize 1000;
 
SELECT   s.sid, s.username, u.tablespace, 
         s.sql_hash_value||'/'||u.sqlhash hash_value, 
         s.sql_id, u.segtype, u.contents, u.blocks
FROM     v$session s, v$tempseg_usage u
WHERE    s.saddr=u.session_addr
ORDER BY u.blocks DESC;

2. The query above might show different segment types (the SEGTYPE column). Most of the time, the SORT segment type is the one we need to check. 

3. TEMP tablespace may also be used by any open cursor in a given session.  The SQL query found above may not necessarily be the culprit. If the query above returns a null value for the SQL_ID, get the SID of the row and check it from V$SQL using the following:  

col hash_value for 999999999999

SELECT   hash_value, sorts, sql_id, rows_processed/executions
FROM     v$sql
WHERE    hash_value in 
         (SELECT hash_value FROM v$open_cursor 
         WHERE sid = <sid from the session with the most blocks from the query above>)
AND      sorts > 0
AND      PARSING_SCHEMA_NAME='AVUSER'
ORDER BY rows_processed/executions;

4. For each suspected query, run the query below to get the explained plan:

SELECT * FROM table(dbms_xplan.display_awr(sql_id=>'<sql_id from one of the queries above>'));

• 000027944 -- How to check local file system disk space usage for RSA Identity Governance & Lifecycle 
• 000027928 -- Determining how much space is used in the Oracle Automatic Storage Management (ASM) partition on an RSA Identity Governance & Lifecycle hardware appliance  

1. Login as the oracle user and connect to the database as user SYS using the command below or by using SQL Developer. 

sqlplus "/as sysdba"

2. Execute the following SQL depending on the appliance type.

• On a hard appliance using ASM partitioning:

ALTER TABLESPACE "TEMP" ADD TEMPFILE '+DG01' SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
COMMIT;

• On a soft appliance that uses a local filesystem:

ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u01/app/oracle/oradata/AVDB/tempXX.dbf' 
SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 
COMMIT;

$ ls /u01/app/oracle/oradata/AVDB/ | grep temp

This command may take two to five minutes to complete.

On non-appliances, please contact your DBA.
 

WARNING:  This solution is intended to be used where the original TEMP space allocated was insufficient for the normal operation of the database.  If extending the TEMP tablespace does not immediately resolve the issue do not continue to allocate more TEMP tablespace.  Instead, attempt to understand why the problem query is too complex to complete with the default TEMP space.  Be aware that if there is a query that is running indefinitely (not completing) it may require an infinite amount of TEMP space and you cannot configure TEMP tablespace as a solution.

In general, if a customer follows the TEMP tablespace recommendations in our Installation Guide they should not need to add additional TEMP tablespace.