000028161 - Getting error ORA-01652 in tablespace TEMP using RSA Identity Governance & Lifecycle

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

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

Technical Notes: Temporary tablespaces are used to manage space for database sort operations and or for storing global temporary tables.  For example, if you join two large tables and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation.

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 one or more of the queries below:
   
   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(ies) that are using lots of temp tablespace in Oracle, run the following:

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;

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;

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

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 command below to add a segment temp file:

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

This command may take two to five minutes to complete.