000028161 - ORA-01652 : unable to extend temp segment by 128 in tablespace TEMP in RSA Identity Governance & Lifecycle

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 explain 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.