ORA-01652 or ORA-1652
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.
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
------- --- ----- -------------- ------------------------------ ---------- ------
SCOTTI 348 9358 2811359775 enqueue 16:51:44 80235
SCOTTI 348 9358 2811359775 direct path read 16:51:44 56735
SCOTTI 348 9358 2811359775 direct path write 16:51:44 26042
SCOTTI 348 9358 2811359775 DFS lock handle 16:51:44 9953
SCOTTI 348 9358 2811359775 local write wait 16:51:44 5312
SCOTTI 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;
Later on, you may consider to reduce the overall size of tempfiles to reclaim some space.