How to Resolve ORA-01652 or ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

  • by
Found repeated errors in one alert log of RAC:
Fri Oct 10 17:12:10 2015
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
...

We should solve problem as soon as possible in order to make TEMP available to other transactions. But first, we needed to know who was (were) occupying TEMP tablespace for a long time.
[oracle@oracle9i sql_scripts]$ vi ListActiveSession.sql
...
set pages 100;
set linesize 150;
column username format a7;
column "OS Process ID" format a5;
column sql_hash_value format 9999999999;
column event format a30;
alter session set nls_date_format='hh24:mi:ss';

SELECT S.USERNAME,
  S.SID,
  P.SPID "OS Process ID",
  s.sql_hash_value,
  SE.EVENT,
  S.LOGON_TIME,
  SUM(SE.TIME_WAITED) WAITED
FROM V$SESSION S,
  V$SESSION_EVENT SE,
  V$PROCESS P
WHERE S.SID     =SE.SID
AND S.PADDR     =P.ADDR
AND S.STATUS    ='ACTIVE'
AND S.USERNAME IS NOT NULL
AND TIME_WAITED > 0
GROUP BY S.USERNAME,
  S.SID,
  p.SPID,
  s.sql_hash_value,
  SE.EVENT,
  S.LOGON_TIME
ORDER BY WAITED DESC;

Let's see the result:
SQL> @ListActiveSession.sql

USERNAM SID OS Pr SQL_HASH_VALUE EVENT                          LOGON_TIME WAITED
------- --- ----- -------------- ------------------------------ ---------- ------
EDCHEN  348 9358      2811359775 enqueue                        16:51:44    80235
EDCHEN  348 9358      2811359775 direct path read               16:51:44    56735
EDCHEN  348 9358      2811359775 direct path write              16:51:44    26042
EDCHEN  348 9358      2811359775 DFS lock handle                16:51:44     9953
EDCHEN  348 9358      2811359775 local write wait               16:51:44     5312
EDCHEN  348 9358      2811359775 SQL*Net more data from dblink  16:51:44     1385
...

The user is waiting for a lock (enqueue) for some reasons. Therefore, he can't release the space of TEMP. You have to kill the session so as to release TEMP, or add a new data file for temp.
SQL> alter tablespace temp_tbs add tempfile 'temp05.dbf' size 10M autoextend on;

Leave a Reply

Your email address will not be published. Required fields are marked *