Alter tablespace add datafile

Suppose our database getting full. In that situation we have to increase the size of database by adding datafile in existing tablespace or creating new tablespace along with datafile.

To add datafile in existing tablespace follow the below steps:-

1. Check the existing file in Tablespace:-

SQL>select file_name,bytes/1024/1024 SIZE_IN_MB from dba_data_files where tablespace_name=’XXCUSTOM’;

2. Add datafile:-

SQL> alter tablespace xxcustom add datafile ‘/data01/prod/xxcustom03.dbf’ size 5g;

For additional information on Administering Database size . 👉 Check how to resize the datafile in oracle database.