Oracle – ORA-01653 – unable to extend table in tablespace – Fix

Error: ORA-01653: unable to extend table in tablespace

Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.

Note: Change the table space name for that you need the information in the below Scripts (Script: 1 & 2)

Solution:

Script: 1

Execute the below query to find the Used Size, Free Size, Total Size of a table space

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name 
and df.tablespace_name = 'MYTABLESPACE';

 

Script: 2

Find the file name/location of your tablespace

SELECT file_name, tablespace_name FROM dba_data_files 
where TABLESPACE_NAME = ' MYTABLESPACE';

 

Script: 3

Increase the table space Size

ALTER DATABASE DATAFILE 'file_name from the Script: 2’
RESIZE 5000M;

 

Script: 4

Enable the AUTOEXTEND

ALTER DATABASE DATAFILE 'file_name from the Script: 2’
AUTOEXTEND ON;

 

Script: 5

Check either AUTOEXTEND ON or OFF

Select TABLESPACE_NAME, file_name, AUTOEXTENSIBLE 
from dba_data_files

If you have comment/suggestion please post.