Oracle Extend Tablespace

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

When the tablespaces of the database are full, you will no longer be able to add or remove data from these tablespaces.

There are several ways to extend a tablespace.

Extending a tablespace by adding a new datafile #

If you use a smallfile tablespace, you can use ALTER TABLESPACE statement to add a more datafile to the tablespace:

ALTER TABLESPACE tablespace_name
    ADD DATAFILE 'path_to_datafile'
    SIZE size;Code language: SQL (Structured Query Language) (sql)

For both smallfile and bigfile tablespaces, you can use the AUTOEXTEND ON clause. Oracle will automatically extend the size of the datafile when needed:

ALTER TABLESPACE tablespace_name
    ADD DATAFILE 'path_to_datafile'
    SIZE size    AUTOEXTEND ON;Code language: SQL (Structured Query Language) (sql)

Let’s see the following example.

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

CREATE SMALLFILE TABLESPACE tbs10 
    DATAFILE 'tbs10.dbf' SIZE 10m;Code language: SQL (Structured Query Language) (sql)

Next, create a new table t1 whose tablespace is tbs10:

CREATE TABLE t10(id INT PRIMARY  KEY) 
TABLESPACE tbs10;Code language: SQL (Structured Query Language) (sql)

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

BEGIN
    FOR counter IN 1..1000000 loop
        INSERT INTO t10(id)
        VALUES(counter);
    END loop;
END;
/Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-01653: unable to increase tablespace TBS10 by 1MB during insert or update on table OT.T1Code language: SQL (Structured Query Language) (sql)

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:

ALTER TABLESPACE tbs10
    ADD DATAFILE 'tbs10_2.dbf'
    SIZE 10m
    AUTOEXTEND ON;Code language: SQL (Structured Query Language) (sql)

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

BEGIN
    FOR counter IN 1..1000000 loop
        INSERT INTO t10(id)
        VALUES(counter);
    END loop;
END;
/

You can retrieve the number of rows from the t1 table:

SELECT count(*) FROM t1;Code language: SQL (Structured Query Language) (sql)

Here is the output:

COUNT(*)
----------
1000000Code language: SQL (Structured Query Language) (sql)

Extending a tablespace by resizing the datafile #

You can use the ALTER DATABASE RESIZE DATAFILE statement to extend the size for smallfile and bigfile tablespaces:

ALTER DATABASE
    DATAFILE 'path_to_datafile'
    RESIZE size;Code language: SQL (Structured Query Language) (sql)

For example:

First, create a new tablespace called tbs11:

CREATE TABLESPACE tbs11
    DATAFILE 'tbs11.dbf'
    SIZE 10m;Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE t11(
    c INT PRIMARY KEY
) TABLESPACE tbs11;Code language: SQL (Structured Query Language) (sql)

Then, query the size of the tablespace tbs11:

SELECT 
    tablespace_name, 
    SUM(bytes)/1024/1024 AS total_mb 
FROM 
    dba_data_files 
WHERE 
    tablespace_name = 'TBS11'
GROUP BY tablespace_name;Code language: SQL (Structured Query Language) (sql)

Output:

TABLESPACE_NAME                  TOTAL_MB
------------------------------ ----------
TBS11                                  10Code language: SQL (Structured Query Language) (sql)

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

ALTER DATABASE 
    DATAFILE 'tbs11.dbf' 
    RESIZE 15m;Code language: SQL (Structured Query Language) (sql)

Finally, query the total size of the tbs11 tablespace:

SELECT 
    tablespace_name, 
    SUM(bytes)/1024/1024 AS total_mb 
FROM 
    dba_data_files 
WHERE 
    tablespace_name = 'TBS11'
GROUP BY tablespace_name;Code language: SQL (Structured Query Language) (sql)

Here is the output:

TABLESPACE_NAME                  TOTAL_MB
------------------------------ ----------
TBS11                                  15Code language: SQL (Structured Query Language) (sql)

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

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

Summary #

• Use ALTER TABLESPACE ... ADD DATA FILE to extend a smallfile tablespace.
• Use ATLER DATABASE ... RESIZE to extend a smallfile or bigfile tablesppaces.