unable to extend table by 8192 in tablespace system

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;

Related posts

Default image

Error: ORA-16765: Redo Apply is running

Default image

Khắc phục lỗi recovery khi OPEN RESETLOGS lỗi SYSTEM trong Oracle Database?

Default image

check the FRA usage and troubleshoot the issue in oracle DB