ORA-01652: unable to extend temp segment: Solutions

ORA-01652

We receive this error 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. There are three ways to do  it

  1. Add datafile
  2. Resize the existing datafile
  3. autoextent the datafile

Each one is explained below with example below

we  can use ALTER TABLESPACE to add datafile in tablespace like

Default Tablespace

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 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;

or we  can use ALTER TABLESPACE resize datafile in tablespace like

Default Tablespace

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 ASM

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

 

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;

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

TEMP table space
We can get the total space in the TEMP tablespace using the commandselect sum(bytes)/1024/1024/1024  Total_space from dba_temp_files;Free space using the commandFor Non RACSELECT 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;

Leave a Reply