Resolving ORA-01652

Resolving ORA-01652

Steps we can follow to resolve the error ORA-01652 is as follows:

Solution 1:

We can check for held TEMP segments with this query:
SQL> set linesize 1000
SQL>select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;


TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 201 169216 70912 15 5 TEST1 oracle ACTIVE
TEMP 201 178688 128 9 1450 TEST2 INACTIVE
TEMP 201 240128 128 150 299 APEX_PUBLIC_USER apex_admin INACTIVE



This command may remove a TEMP segment, try:

alter tablespace xxxxx coalesce;

alter tablespace TEMP coalesce;

(OR)

Solution 2:

We can also use a drop segments event to remove temporary space from a tablespace:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';

where:
x is the value for file# from Tablespace.

(OR)

Solution 3:Add the temp file for Temporary tablespace

SQL> SELECT FILE_NAME||' '||TABLESPACE_NAME||' '||BYTES/1024/1024
2 FROM DBA_TEMP_FILES;

FILE_NAME||''||TABLESPACE_NAME||''||BYTES/1024/1024
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u04/TESTDB/TESTDB_temp_01.dbf TEMP 1900

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/TESTDB/TESTDB_temp_02.dbf' SIZE 200M;

Tablespace altered.


SQL> SELECT TABLESPACE_SIZE/1024/1024||' '||ALLOCATED_SPACE/1024/1024||' '||FREE_SPACE/1024/1024
FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_SIZE/1024/1024||''||ALLOCATED_SPACE/1024/1024||''||FREE_SPACE/1024/1024
--------------------------------------------------------------------------------------------------------------------------
2100 1898 1519

SQL> select tablespace_name, total_blocks, used_blocks, free_blocks
2 FROM v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP 242688 74112 168576


SQL> set linesize 1000

SQL> SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;

TABLESPACE_NAME SUM(BYTES_USED)/1024/1024 SUM(BYTES_FREE)/1024/1024
------------------------------ ------------------------- -------------------------
TEMP 1898 202


Then,resize or add the datafile related to a Tablespace if require or add a new datafile to current tablespace


Addind Datafile to existing Tablespace:
ALTER TABLESPACE RMRTM ADD DATAFILE '/u05/TESTDB/RMRTM_DATA_01.DBF' SIZE 200M;


SQL> SELECT *FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE '%RM%';

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/u04/TESTDB/RMRTM_DATA_01.DBF 39 RMRTM 3221225472 393216 AVAILABLE 39 YES 3221225472 393216 12800 3220176896 393088 ONLINE



SQL> SET LINESIZE 1000
SQL> SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACEF.FREE_SPACE),'999,999')"USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;


TABLESPACE_NAME USEDMB FREEMB TOTALMB FREE
------------------------------ -------- -------- -------- ------

RMRTM 2,748 324 3,072 11 %


SQL>ALTER TABLESPACE RMRTM ADD DATAFILE '/u05/TESTDB/RMRTM_DATA_01.DBF' SIZE 200M;

Tablespace altered

(OR)

we can resize the existing Datafile if the mount point have enough space

Note:
Resizing Datafile can be done as givne below:

ALTER DATABASE DATAFILE 'D:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE 200M;

(OR)

Solution 4:Create a new Temporary Tablespace and make this temporary tablespace default for our Database.

Step 1:Create the new temporary Tablespace

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/u05/TESTDB/TEMP02.dbf' SIZE 256M
REUSE AUTOEXTEND ON NEXT 256M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;

Tablespace created.

Step 2:Make this temporary tablespace default at Database level

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Database altered.

Where:
'TEMP02' is the DEFAULT TEMPORARY TABLESPACE name in the above case.

Step 3:Bounce our Database so that actual temporary space is release from 'temp' Tablespace and Drop tablespace 'temp' including content

we have to Make sure We delete on temp data files and the actual space is recovered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 855641944 bytes
Database Buffers 205520896 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
SQL> Drop tablespace temp including contents;

Tablespace dropped.

SQL> select *from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u05/TESTDB/TEMP02.dbf
3 TEMP02 268435456 32768 ONLINE
1 YES 8589934592 1048576 32768 267386880 32640

Now,we can see the new Temporary tablespace is 'TEMP02' with enough free space.

Note:We can adopt either of one solution