This is a very frequent issue where we face ORA-01650 / ORA-01652 / ORA-01653 / ORA-01654 and ORA-01658 or related errors complaining that Oracle is unable to extend the tablespace, which means the mentioned tablespace has been exhausted and we need to add space to the tablespace.

1) Identifying which tablespace is exhausted:-

This can be identified from the error message itself:-

ORA-01653: Unable To Extend Table %s.%s By %s In Tablespace <tablespace name>
ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace <tablespace name>

So is the actual tablespace that has been exhausted.

2) Querying the associated datafiles & space for the identified tablespace:-

col file_name form a80
set lines 190
set pages 3000
select tablespace_name , file_name , sum(bytes)/1024/1024 "Size in MB" from dba_data_files
where tablespace_name = '&tablespace_name'
group by  tablespace_name , file_name
order by file_name
/

Running above sql would prompt for tablespace_name, please enter the which we identified in step 1.

The output would be something similar to:-

TABLESPACE_NAME                FILE_NAME                                                                        Size in MB
------------------------------ -------------------------------------------------------------------------------- ----------
USERS                          /apps/test/data01/ISM01/users01.dbf                                                   500

No. of rows returned from the above query is completely dependent on your environment. From the above output we can see the number of datafiles associated with the tablespace along with their size.

3) Adding space to the tablespace:-

Now we have to decide between, resizing an already existing datafile or adding a new datafile, both steps are described below, but this call is completely on the DBA depending upon the storage capacity on different filesystems.

3.a) Resizing an existing datafile:-

ALTER DATABASE DATAFILE '<FILE_NAME from Step 2>' resize <total amount of space for this datafile in K|M|G>;

eg:-

ALTER DATABASE DATAFILE '/apps/test/data01/TEST01/users01.dbf' RESIZE 1024M;

To run the above DDL you would require “ALTER DATABASE” privilege or a user whose been granted with this privilege via a ROLE.

You can again run the SQL in Step 2 to confirm the change.

TABLESPACE_NAME                FILE_NAME                                                                        Size in MB
------------------------------ -------------------------------------------------------------------------------- ----------
USERS                          /apps/test/data01/ISM01/users01.dbf                                                   1024

3.b) Adding a datafile:-

In cases where one filesystem is full or the existing datafiles have reached operating system limit or purely because of better management you would require to add datafiles in a tablespace, this can be achieved via below DDL:-

ALTER TABLESPACE ADD DATAFILE 'New <FILE_NAME>' size <amount of space you want to add to this datafile in K|M|G>;

eg:-

ALTER TABLESPACE ADD DATAFILE '/apps/test/data01/ISM01/users02.dbf' SIZE 500M;

To run this DDL you would require ALTER TABLESPACE privilege or a user whose been granted with this privilege via a ROLE.

You can check this change by querying the tablespace details from the query in Step 2.

TABLESPACE_NAME                FILE_NAME                                                                        Size in MB
------------------------------ -------------------------------------------------------------------------------- ----------
USERS                          /apps/test/data01/TEST01/users01.dbf                                                   500
USERS                          /apps/test/data01/TEST01/users02.dbf                                                   500

Finally you can execute below SQL to get the details of the space for the tablespace in concern:-

select  a.TABLESPACE_NAME,
a.BYTES/1024/1024 TOTAL_MB,
b.BYTES/1024/1024 FREE_MB,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from    dba_free_space
group   by TABLESPACE_NAME
 )
b
where   a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME='&tablespace_name'
order by a.TABLESPACE_NAME
/

Above query would prompt for the tablespace name, please enter your that you identified in Step 1 and you’ll see the TOTAL space allocated to the tablespace in MB along with the free space in MB and the used percentage.

In the next post I would share the shell scripts that can be configured in cron to sent alert if any tablespace is used above a set threshold so that these kind of issues can be dealt with proactively.