Mir Sayeed Hassan – Oracle Blog

Increase or decrease the size of Tablespace and Datafiles in Oracle database 11gR2

 To Increase/extend the Size of a tablespace

Method 1 – Increase/Extend the size of the tbs using the resize keyword as shown below:

sys@TESTDB> select INSTANCE_NAME, VERSION from v$instance;

INSTANCE_NAME    VERSION
--------------------- ------
testdb           11.2.0.4.0

Consider the below testing example:

sys@TESTDB> create tablespace test_tbs datafile '/u01/app/oracle/oradata/prim/test_dbs.dbf' size 100m;
Tablespace created.

sys@TESTDB> select name from V$datafile;
---
---
/u01/app/oracle/oradata/prim/test_dbs.dbf

Now extend the datafile size by using resize

sys@TESTDB> alter database datafile '/u01/app/oracle/oradata/prim/test_dbs.dbf' resize 200m;
Database altered.

Method 2:

Extend the size of tbs by adding the new datafile to the existing tbs, This scenario is used when the existing size of the tbs is reached or OS file size is not free

 This is to add to same location:

sys@TESTDB> alter tablespace TEST_TBS add datafile '/u01/app/oracle/oradata/prim/test2.dbf' size 100m;
Tablespace altered.

This is to add to another location:

sys@TESTDB> alter tablespace TEST_TBS add datafile '/u02/oradata/datafiles/test2.dbf' size 100m;
Tablespace altered.

Method 3:

Extend by using the AUTO EXTEND OPTION of the datafile, Therefore Oracle will automatically increase the size of the datafile until it reaches to the maximum limit & also you can limit the auto-extend as per your requirement.

Extend by using auto extend option to the default

sys@TESTDB> alter database datafile '/u01/app/oracle/oradata/prim/test2.dbf' autoextend on;
Database altered.

Limit the auto-extend size as per your requirement

sys@TESTDB> alter database datafile '/u01/app/oracle/oradata/prim/test2.dbf' autoextend on next 10m maxsize 300m;
Database altered.

There is an option to auto extend while creation of the tablespace itself

sys@TESTDB> create tablespace test1_tbs datafile '/u01/app/oracle/oradata/prim/test1.dbf' size 100m autoextend on next 200m maxsize 500m;
Tablespace created.

To decrease the size of a tablespace

Decrease the size of tbs by decreasing the datafile present in it as shown below

Verify the size of the tablespace

sys@TESTDB> @/home/oracle/scripts/checktbs.sql;

Tablespace                        Used MB    Free MB   Total MB      Pct. Free
------------------------ ---------- ---------- ---------- --------------------------
SYSAUX                            6087        309        6396         5
UNDOTBS1                          15          5320       5335         100
USERS                             311         289        600          48
SYSTEM                            34495       1805       36300        5
EXAMPLE                           1           49         50           98
TEST_TBS                          2           298        300          99 – Need to decrease the size

The associated datafile is:

/u01/app/oracle/oradata/prim/test_dbs.dbf

sys@TESTDB> alter database datafile '/u01/app/oracle/oradata/prim/test_dbs.dbf' resize 50m;
Database altered.

Hence its has resized as this tablespace doesn’t have sufficient data in it.

=============Hence tested & verified in our test env=============