ORA-01652: unable to extend temp segment: Solutions

Last updated on July 10th, 2019 at 05:21 am

ORA-01652

We receive this error  ORA-01652 while inserting data,creating index, table when we don’t have enough space in tablespace.

Solution is to add sufficient space in the tablespace identified in the error ORA-01652 There are three ways to do  it

  1. Add datafile
  2. Resize the existing datafile
  3. auto-extent the datafile

This can happen with permanent tablespace  and Temporary tablespace Each one is explained below with example below

Permanent Tablespace

  1. we  can use ALTER TABLESPACE to add datafile in tablespace like

Syntax

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile>

Example

ALTER TABLESPACE TEST ADD DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ SIZE 1000M

With Oracle ASM

ALTER TABLESPACE TEST ADD DATAFILE ‘+DATA ‘ SIZE 1000M;

Alter tablespace to add multiple datafiles

SQL>alter tablespace tablespace_name add datafile
<’destination 1’> size 10M autoextend on next 124M,
<’destination 2’> size 10M autoextend on next 124M,
<’destination 3’> size 10M autoextend on next 124M;

2. we  can use ALTER TABLESPACE resize datafile in tablespace like

First find the location of present datafiles in the tablespace

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like ‘%&1%’;

Then look at the space in the mount where datafile is present using

df -k command

And then resize the datafile

Syntax

Alter  database datafile <datafile>  resize <size>;

Example

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like ‘EXAMPLE_DB’;

/u01/oracle/testdb/example_db01.dbf        1000

Alter  database datafile ‘/u01/oracle/testdb/example_db01.dbf’   resize 2000M;

With Oracle ASM diskgroups

ALTER DATABASE DATAFILE  ‘+DATA/testdb/example_db01.dbf’   resize 2000M;

3. How to modify the existing Datafile  auto extend characteristics in a Tablespace

you can modify datafile  auto extend   using alter database datafile option

Syntax
ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;
Example
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE 2400M;

TEMP tablespace

If this is happening for the temporary tablespace, we can add temp-file to get it working and at the same time we can look out for the session using high temporary tablespace to clear the temp space

We can get the total space in the TEMP tablespace using the command

select sum(bytes)/1024/1024/1024  Total_space from dba_temp_files;

Free space using the command

For Non RAC

SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;
For RAC

select inst_id, tablespace_name, segment_file, total_blocks,  used_blocks, free_blocks, max_used_blocks, max_sort_blocks  from gv$sort_segment;

And then add temp file using the below command

Syntax

ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size>

Example

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oracle/TEST/oradata/temp_4.dbf’ SIZE 1000M;

With ASM

ALTER TABLESPACE TEST ADD TEMPFILE ‘+DATA ‘ SIZE 1000M;

We can check for the session using temp segment using the query

col inst_id format 999
col sid format 99999
col status format a11
col program format a20 trunc
col machine format a17 trunc
col action format a39
col module format a39
col blocks heading "TEMP BLOCKS"
SELECT b.blocks, s.inst_id, s.sid, s.status,
s.action,
s.program,
s.machine,
s.module
FROM gv$session s,
gv$sort_usage b
WHERE s.saddr = b.session_addr
and s.inst_id = b.inst_id
and b.blocks > 100000
order by b.blocks desc;

Hope you like the details on ORA-01652 error and solutions . Please do let me know the feedback on it

Related Article

How to check tablespace in Oracle Database

What you need to know about create tablespace statement in Oracle

How to shrink the datafile in Oracle

How to move LOB segment to another tablespace

Leave a Reply