Skip to content

How to Resolve ORA-01652: unable to extend temp segment by 8192 in tablespace

  • by

ORA-01652 or ORA-1652

1. Permanent Tablespace

SQL> INSERT INTO ERP35.PAY_HIST SELECT * FROM ERP35.PAY_TRANS;
INSERT INTO ERP35.PAY_HIST SELECT * FROM ERP35.PAY_TRANS;
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8192 in tablespace ERP35_TBS01

ORA-01652 means that there's no room for new coming data in the tablespace, you should either delete some data or add some data files to it.

To add a data file to the tablespace, you can do it like this:

SQL> alter tablespace ERP35_TBS01 add datafile '/u01/app/oracle/oradata/ORCL/ERP35_TBS01_02.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

2. Temporary Tablespace

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.

Solutions

To solve the problem, 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 '/u01/app/oracle/oradata/ORCL/temp05.dbf' size 10M autoextend on next 10m maxsize unlimited;

Leave a Reply

Your email address will not be published.