Cthunter287

Summary: in this tutorial, you will learn how to extend the size of a tablespace in the Oracle Database.

When tablespaces of the database are full, you will not able to add or remove data on these tablespaces anymore.

This post provides the steps to resize the Undo tablespace.ie, to add space or shrink the current Undo tablespace. Shrinking Undo Tablespace Size. Undo space once allocated will be available for reuse but will not be deallocated to the OS. The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace. How can we shrink temp tablespace in oracle? And why it is increasing so much like upto 25 GB since there is only one schema in the database for the application and data table space size is 2 GB and index table space size is 1 GB used.

There are a few ways you can extend a tablespace.

Extending a tablespace by adding a new datafile

The first way to extend a tablespace is to add a new datafile by using the ALTER TABLESPACE statement:

If you use the AUTOEXTEND ON clause, Oracle will automatically extend the size of the datafile when needed:

Oracle Alter Temp Tablespace Autoextend

Let’s see the following example.

First, create a new tablespace called tbs10 with the size 1MB:

Next, create a new tablet1 whose tablespace is tbs10:

Then, insert 1,000,000 rows into the t1 table:

Oracle issued the following error:

So the tablespace tbs10 does not have enough space for the 1 million rows.

After that, use the ATLER TABLESPACE statement to add one more datafile whose size is 10MB with the AUTOEXTEND ON option:

Finally, insert 1 million rows into the t1 table. It should work now. This query returns the number of rows from the t1 table:

Here is the output:

Extending a tablespace by resizing the datafile

Another way to extend a tablespace is to resize the data file by using the the ALTER DATABASE RESIZE DATAFILE statement:

Consider the following example.

First, create a new tablespace called tbs11:

Next, create a new table called t2 that uses tbs11 as the tablespace:

Then, query the size of the tablespace tbs11:

The following illustrates the output: Office 2010 key viewer.

After that, use the ALTER DATABASE to extend the size of the datafile of the tablespace to 15MB:

Oracle Create Temporary Tablespace

Finally, query the size of the tbs11 tablespace:

Here is the output:

Oracle Resize Temp Tablespace

As you can see, the size of the tablespace tbs11 has been extended to 15MB.

Note that Oracle does not allow you to add a datafile to a bigfile tablespace, therefore, you only can use ALTER DATABASE DATAFILE RESIZE command.

Oracle Increase Temp Tablespace Size

In this tutorial, you have learned how to extend the tablespace by adding a new datafile to the tablespace or resize existing datafile.