ORA-1653: unable to extend table HR.TEST by 8192 in tablespace DATA_1
An error occurs during the insertion operation:
ORA-1653: unable to extend table HR.TEST by 8192 in tablespace DATA_1
Error: ORA-01653
Text: unable to extend table %s.%s by %s in tablespace %s
-------------------------------------------------------------------------------
Cause: Failed to allocate an extent for table segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
Error: ORA-01654
Text: unable to extend index %s.%s by %s in tablespace %s
-------------------------------------------------------------------------------
Cause: Failed to allocate extent for index segment in tablespace.
Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the specified tablespace.
Check which tablespaces are full or which have free 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)) "% 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;
Check the datafiles size, max size, and auto extended for the datafiles
select TABLESPACE_NAME, AUTOEXTENSIBLE,sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from DBA_DATA_FILES
group by TABLESPACE_NAME;
OR
select TABLESPACE_NAME, AUTOEXTENSIBLE,maxbytes/1024/1024 MAX_SIZE from dba_data_files;
Check for the temp files size, auto extended on, and max size
select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAX_SIZE
from DBA_TEMP_FILES
group by TABLESPACE_NAME;
OR
select tablespace_name, AUTOEXTENSIBLE,maxbytes/1024/1024 MAX_SIZE from dba_temp_files;
Solution: If the tablespace is full then you have the following options:
AUTOEXTEND ON
You check the current size of data files in the tablespace if the current size is equivalent to MAX SIZE then you can enable the auto-extend on and increase the MAXSIZE. If MAXSIZE is already 32 GB, Oracle cannot support exceeding more than 32 GB.
ALTER DATABASE DATAFILE 'C:\oradata\filename.dbf' AUTOEXTEND ON MAXSIZE 10G;
ADD ANOTHER DATA FILE
You can also add another data file into the tablespace If the tablespace is full.
ALTER TABLESPACE tablespacename ADD DATAFILE 'C:\oradata\filename.dbf' SIZE 10G;
INCREASE FILE SIZE
You can increase the file size if its already not on the max size of 32 GB
ALTER DATABASE DATAFILE 'C:\oradata\filename.dbf' RESIZE 10G;