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.
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:
ALTER TABLESPACE tablespace_name
ADD DATAFILE 'path_to_datafile'
SIZE size;
Code language: SQL (Structured Query Language) (sql)
If you 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 1MB:
CREATE TABLESPACE tbs10
DATAFILE 'tbs10.dbf' SIZE 1m;
Code language: SQL (Structured Query Language) (sql)
Next, create a new table t1
whose tablespace is tbs10
:
CREATE TABLE t1(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 t1(id)
VALUES(counter);
END loop;
END;
/
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following error:
ORA-01653: unable to extend table OT.T1 by 8 in tablespace TBS10
Code 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:
SELECT count(*) FROM t1;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
COUNT(*)
----------
1000000
Code language: SQL (Structured Query Language) (sql)
Extending a tablespace by resizing the datafile
Another way to extend a tablespace is to resize the data file by using the ALTER DATABASE RESIZE DATAFILE
statement:
ALTER DATABASE
DATAFILE 'path_to_datafile'
RESIZE size;
Code language: SQL (Structured Query Language) (sql)
Consider the following example.
First, create a new tablespace called tbs11
:
CREATE TABLESPACE tbs11
DATAFILE 'tbs11.dbf'
SIZE 1m;
Code language: SQL (Structured Query Language) (sql)
Next, create a new table called t2
that uses tbs11
as the tablespace:
CREATE TABLE t2(
c INT PRIMARY KEY
) TABLESPACE tbs11;
Code language: SQL (Structured Query Language) (sql)
Then, query the size of the tablespace tbs11
:
SELECT
tablespace_name,
bytes / 1024 / 1024 MB
FROM
dba_free_space
WHERE
tablespace_name = 'TBS11';
Code language: SQL (Structured Query Language) (sql)
The following illustrates the output:
TABLESPACE_NAME MB
--------------- ----------
TBS11 .9375
Code 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 size of the tbs11
tablespace:
SELECT
tablespace_name,
bytes / 1024 / 1024 MB
FROM
dba_free_space
WHERE
tablespace_name = 'TBS11';
Code language: SQL (Structured Query Language) (sql)
Here is the output:
TABLESPACE_NAME MB
---------------- ----------
TBS11 14.9375
Code language: SQL (Structured Query Language) (sql)
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.
In this tutorial, you have learned how to extend the tablespace by adding a new datafile to the tablespace or resizing an existing datafile.