Home » unable to extend table by 8192 in tablespace system

unable to extend table by 8192 in tablespace system

by tuanlp

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;

You may also like