Increase Tablespace Size Automatically and Manually

Oracle allow DBA to set the datafiles to be increase automatically.

Following command is needed to check the NEXT incremental size, and the Maximum Size.

SELECT tablespace_name, file_name, increment_by*[block size]/1024/1024 Inc_MB, maxbytes/1024/1024 MaxMB , autoextensible FROM dba_data_files;

You need to change the [block size] to your own block size, the reason is because the increment_by column is storing number of block we need to convert into Bytes by multiple by block size.

To find your block size use following command

SHOW PARAMETER db_block_size

for my case I have 8192 bytes per Oracle block, therefore my script is

SELECT tablespace_name, file_name, increment_by*8192/1024/1024 Inc_MB, maxbytes/1024/1024 MaxMB , autoextensible FROM dba_data_files;

In my example I want to set one of my MY_TBS tablespaces datafile namely my_tbs_03.dbf to be automatically increase by 5M until 100M.

ALTER DATABASE DATAFILE '/datafiles/edba01/edba01/my_tbs_03.dbf'
AUTOEXTEND ON
NEXT 5M
MAXSIZE 100M;

You also able to set the Maximum grow size to UNLIMITED, which
mean Oracle will fill until the maximum it can go.

Now I want to set my datafile my_tbs_02.dbf to unlimited growth size.

ALTER DATABASE DATAFILE '/datafiles/edba01/edba01/my_tbs_02.dbf'
AUTOEXTEND ON
NEXT 5M
MAXSIZE UNLIMITED;

Please note that:
For the UNLIMITED datafile this is not mean that you datafile is super power kind of datafile that you need to upgrae your storage only. The UNLIMITED means that Oracle will used up to the maximum of 4 millions block per datafile, for my case is equal to 4E6*8192. This is not apply for BIGFILE TABLESPACE.

For ease of maintenance in my opinion it's good to always be automatically extended and with a specified size for the NEXT parameter. Specifying the NEXT parameter assures that datafiles grow with consistent, reusable extent sizes. If extent sizes are too small then a large table could have so many extents to search through that performance will degrade seriously. The NEXT parameter is defaulted to the block size. Do not leave NEXT undeclared and defaulted. Defaulting to the block size is usually too small. Only small static tables and indexes could have sizes for NEXT of below 1M. For some tables well over 1M is prudent.

It will be better you set the MAXSIZE value to non UNLIMITED


There are two ways to increase tablespace size Manually.

1. Add new datafile to the tablespace.
2. Increase the datafile size of the tablespace.

Note: You can use this script to check the tablespace size and autoextensible status

SELECT tablespace_name, file_name, bytes/1024/1024 MBytes, autoextensible FROM dba_data_files;

1. Add new datafile to the tablespace.

In my example I want to add new datafile my_tbs_03.dbf with size 5 megabytes to my_tbs
The command is

ALTER TABLESPACE my_tbs ADD DATAFILE '/datafiles/edba01/edba01/my_tbs_03.dbf' SIZE 5m;

2. Increase the datafile size of the tablespace.

In my example I want to increase the my_first_tbs tablespace's datafile my_first_tbs.dbf from 10m to 15m
The command is

ALTER DATABASE DATAFILE '/datafiles/edba01/edba01/my_first_tbs_01.dbf' RESIZE 15M;

Please note that, there is no command say to increase datafile, the command is to resize the datafile, therefore you may shrink the datafile by using this command with intention of reclaiming space in your storage. There is a limit how small the datafile can be shrink because you can not shrink datafile size below the objects in the datafile. In order to properly reclaimed space check my other article about how to reclaimed space.