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

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Sep 5, 2019
Version 4Show Document
  • View in full screen mode

Article Content

Article Number000028161
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Product/Service Type: Appliance
RSA Version/Condition: All 
IssueThe 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. 

CauseTemporary 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.
 
ResolutionIf 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"


  1. 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;



  1. 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%';


  1. 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';


  1. 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;



  1. 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;


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

  1. 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;


  1. 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>'));
WorkaroundIf 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:
Note that due to a database limit of 32 GB per TEMPFILE extent, if any of the extents were created or show 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"


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

Attachments

    Outcomes