ORA-1653 unable to extend table %s.%s by # in tablespace %s

Applies to:

All Oracle versions
Information in this document applies to any platform.

Cause:

The reason for "ORA-1653 unable to extend table %s.%s by # in tablespace %s" is tablespace is full. No free space left to store data in tablespace.

Solution:

Following are the solutions for this issue.
  1. Try to find out can we reclaim some space from tablespace itself ?
  2. One very common solution for this problem is to re size datafile of tablespace.
  3. If your datafile has reached maximum limit you can add another datafile in your tablespace.
Let's explorer all one by one.

1. Reclaim some space from tablespace: To reclaim some space from tablespace user following commands

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/(1024*1024*1024), OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME LIKE 'TEST_DATA';

SEGMENT_NAME    SEGMENT_TYPE, BYTES/(1024*1024*1024), OWNER
BIN$mzdbm0LiEyHgQFsKzDB5JQ==$0 TABLE 44 TEST_SCHEAM
BIN$m7FTbc4+L2DgQFsKzDBtMw==$0 TABLE 44 TEST_SCHEAM
BIN$m7FA8Zm2jh/gQFsKzDBzaw==$0 TABLE 44 TEST_SCHEAM
BIN$mzc/sAZUT0/gQFsKzDAGUQ==$0 TABLE 43 TEST_SCHEAM
BIN$m7FTbc4/L2DgQFsKzDBtMw==$0 TABLE 40 TEST_SCHEAM
BIN$mjNEIt1tKoTgQFsKzDB7bQ==$0 TABLE 40 TEST_SCHEAM
BIN$m7HFDawRJaHgQFsKzDAKNA==$0 TABLE 40 TEST_SCHEAM
BIN$mjNWBpOaaGHgQFsKzDAM8w==$0 TABLE 40 TEST_SCHEAM
BIN$mzc/sAZVT0/gQFsKzDAGUQ==$0 TABLE 39 TEST_SCHEAM
BIN$nMvx2JPr+8/gQFsKzDAJYg==$0 TABLE 39 TEST_SCHEAM
BIN$mzcNkR9oMtrgQFsKzDA8Ig==$0 TABLE 39 TEST_SCHEAM
BIN$nMv3zaYY2WngQFsKzDAaDA==$0 TABLE 39 TEST_SCHEAM
BIN$mjLomEzdB+7gQFsKzDBIag==$0 TABLE 38 TEST_SCHEAM

If these type of object are there in your tablespace. Then first question comes in to mind is What is this "BIN$mjLomEzdB+7gQFsKzDBIag==$0" ?

Ans: These are recycle bin objects. When user drop any table or index it goes to recycle bin but occupies same space as object space. So we can reclaim this space by purging recycle bin.

connect to the TEST_SCHEMA i.e user who owns these objects using

[oracle@~]$ sqlplus test_schema/test_schema

SQL> purge user_recyclebin;

Recyclebin purged.

Now crosscheck using same command

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/(1024*1024*1024), OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME LIKE 'TEST_DATA';

Now you will not see these objects and you will also get same size of free space as of these objects size.  This is the one case for reclaiming free space. There can be some other scenarios like table fragmentation. Use following link for it Remove Table fragmentation

If these scenarios doesn't apply for the database then.

2. Re size datafile of tablespace: You can resize datafile of tablespace by following commands

 SQL> SELECT FILE_NAME, bytes/(1024*1024*1024) from dba_data_files where tablespace_name like 'TEST_DATA';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/(1024*1024*1024)
----------------------
/opt/oracle/oradata/test/test_data.dbf
               1

SQL> host df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       97G   38G   54G  42% /
/dev/sda1              99M   13M   82M  13% /boot

Before resizing the datafile check below.
  • Free space is available at mount point where datafile is placed. In this case 54G of free space is available at "/opt/oracle". So we can go forward for resizing.
  • Datafile has not reached OS file size limit. If database block size is 8K than maximum datafile size can by 32G. If datafile size has reached 32G then add new datafile.
Here datafile is of size 1G only so we can resize this.

SQL> alter database datafile '/opt/oracle/oradata/test/test_data.dbf' resize 10G;

 Crosscheck datafile is re sized using same command.

SQL> SELECT FILE_NAME, bytes/(1024*1024*1024) from dba_data_files where tablespace_name like 'TEST_DATA';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/(1024*1024*1024)
----------------------
/opt/oracle/oradata/test/test_data.dbf
                     10
If resizing of datafile is not possible use below.

3. Adding datafile to the Tablespaces: Use following commands to add datafile to the tablespaces

SQL> alter tablespace test_data  add datafile '/opt/oracle/test01/dbs/test_data01.dbf' size 1G;

Tablespace altered.

 crosscheck using command

SQL> select file_name from dba_data_files where tablespace_name like 'TEST_DATA';

here you will see name and size of both existing and newly added datafiles.

2 comments: