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

[#1]

Article Number

000028161

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Product/Service Type: Appliance
RSA Version/Condition: All 

Issue

The following error is displayed in the log files (including the aveksaServer.log, alert_AVDB.log, and WorkPoint.log) :

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. 

Cause

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.

This error occurs when the database operation required has failed to allocate an extent file (of the required number of blocks indicated) for a temporary segment in the TEMP tablespace.
 

Resolution

If you receive this error, this may indicate a problem with the underlying application packages. To make that determination, save the java stack trace from the log file where this error was found, along with the results from the trouble-shooting queries below and submit a new support ticket to RSA Identity Governance & Lifecycle Support for further analysis.

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>'));

Workaround

If the number from the queries above shows that TEMP tablespace is full and/or each segment has reached 32GB, then increase TEMP tablespace size as a workaround. This workaround assumes you have available disk space on your system. Please refer to the following RSA Knowledge Base Articles for more information on disk space usage:

• 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  

Note that due to a database limit of 32 GB per TEMPFILE extent, if any of the extents were created or shown as "Unlimited" the maximum size is still 32 GB.

To increase the size of the TEMP tablespace, add another TEMPFILE to the TEMP tablespace.

On an 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;

  where the 'XX' in 'tempXX.dbf' is the next sequential number available. To see the already existing TEMPFILES:

 

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

Notes

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.