Oracle: Extend or Decrease the Size of a Tablespace

The size of Tablespace is combination of  size of ALL individual datafiles, You can enlarge a database in four ways:

Add a datafile to a tablespace

You had ready created a tablespace, due to the restriction of size of  existing datafile is reached largest size you defined before or the drive where the file is existing does not have free space, to add a new datafile to an existing tablespace can be effective way to enlarge the size of tablespace. Simply follow the below command to increase 200M to workspace tablespace01. Notice file tablespace01_02.dbf will be created and allocated to 200M.

SQL>alter tablespace01 add datafile  ‘/us/oracle/ora/tablespace01_02.dbf’ size 200M;

Add a new tablespace

When your databse is not enough it might be a good idea to add a new tablespaces. 

We have another chapter about how to Create Tablespaces in Oracle in detail.

Increase/Decrease the size of a datafile

If you still want to use the existing datafile, You can extend the size of a tablespace by increasing the size of an existing datafile by typing the following command.Suppose the size of existing tablespace01 is 100M,

SQL> alter  database tablespace01 datafile ‘/us/oracle/ora/tablespace01.dbf’ resize 500M;

This will increase the size of Tablespace from 100M to 200M

SQL> alter  database tablespace01 datafile ‘/us/oracle/ora/tablespace01.dbf’ resize 50M;

Otherwise, The initial size 100M will be decreased from 100M to 50M. Notice you can only decrease the empty size of existing Tablespace. so in case of there, tablespace tablespace01has just used spaces less than 50M.

Set auto extend feature of datafile

Meanwhile the tablespace need be extended accordingly by increased objects and index. You can use auto extend feature of datafile. In this, You don’t need manage the the size of datafile by handy.  Oracle will automatically increase the size of a datafile whenever space is required.you just need confirm that have enough free disk spaces allocated. the good point is we can specify by how much size the file should extend and Maximum size to which it should extend.

SQL> alter database datafile /us/oracle/ora/tablespace01.dbf’ auto extend ON next 100M maxsize 1024M;

This command will alter the existing tablespace01.dbf to set auto extend feature ON, the largest size which oracle can automatically increase is 1024M.

SQL> create tablespace tablespace01 datafile ‘/u0/oracle/ora/tablespace01.dbf’ size 100M auto extend ON next 100M maxsize 1024M;

this is to set auto extend feature ON when you create a new tablespace tablespace01.