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

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Sep 12, 2018
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000028161
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Product/Service Type: Enterprise Software
IssueThe following error is displayed in the log files (including aveksaServer.log, alert_AVDB.log, Workpoint.log) :

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.

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

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.

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 resolution queries below and submit a new support ticket to RSA customer support for 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 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
       , 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*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(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;

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. 

However, temp space can 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 had 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
order by rows_processed/executions;

For each suspected query found from the queries above, 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.

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

To increase TEMP tablespace, add another temp file segment file.

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


This command may take two to five minutes to complete.