Index > Oracle > Tips > How to increase the size of an Oracle Tablespace
By Ian Saunders
Increasing the size of a Tablespace
Oracle 8i provides three ways to increase the size of a tablespace.
Now you ask why do we need to increase the size of our tablespaces. Oracle is not like DOS. With DOS or the Windows variants if you keep on adding data to a file the file size just keeps increasing. The size of an Oracle tablespace is the sum of the size of the datafiles that the tablespace contains.
Lets first create our tablespace to use!
Enter the following code into your sql*plus session.
create tablespace fred
datafile 'd:\oracle\oradata\orcl\fred01.dbf'
size 100K;
You may change the tablespace name, datafile location and size to what ever you like.
You should see the output from Oracle:
Tablespace created.
If the filename that you specified in the datafile already exists then you will see the error messages.
ERROR at line 1:
ORA-01119: error in creating database file
'd:\oracle\oradata\orcl\fred01.dbf'
ORA-27038: skgfrcre: file exists
OSD-04010:
Here you have two options, you could either delete the file and rerun the sql command or modify the sql command to specify the reuse parameter.
create tablespace fred
datafile 'd:\oracle\oradata\orcl\fred01.dbf'
size 100K reuse;
Warning: use the reuse parameter with caution as you could overwrite an existing file in error!
Adding an extra datafile to our tablespace
This is the method that is common from version 7.x.x of Oracle upwards. This involves adding an extra datafile to the tablespace hence increasing the sum of the size of the datafiles contained withing the tablespace.
Enter the following code into your sql*plus session.
alter tablespace fred
add datafile 'd:\oracle\oradata\orcl\fred02.dbf'
size 100k;
You should see the message
Tablespace altered.
The same applies to adding datafiles to tablespaces as for creating tablespaces. If the datafile already exists an error message will be returned. You can use the reuse parameter withe the 'alter tablespace' statement.
Resizing an existing datafile
Since Oracle 8.0 it has been possible to resize an existing datafile to increase the size of a tablespace. This feature was not available in earlier releases of Oracle.
Enter the following code into your sql*plus session.
alter database datafile
'd:\oracle\oradata\orcl\fred02.dbf'
resize 1M;
Note: Change the name of the datafile to match the datafile that you created for your tablespace earlier!
You should see the output.
Tablespace altered.
To find out the storage space available for your tablespace enter the following script into your sql*plus session.
select sum(bytes)
from dba_data_files
where tablespace = 'FRED'
Remember to change the tablespace name to the name of your tablespace!
Your output should look something like this:
SUM(BYTES)
----------
204800
Hint: To convert the number of bytes to kilobytes or megabytes just change the first line of you sql to sum(bytes/1024) or sum(bytes/1024/1024) respectively.
Using Autoextend
Another feature added in Oracle 8.0 was the ability to autoextend a datafile should it become full of data. When the datafile is full the autoextend feature will enlarge the datafile incremently up until a predetermined size has been reached.
Enter the following into your sql*plus session.
alter database
datafile 'd:\oracle\oradata\orcl\fred02.dbf'
autoextend on
next 100K
maxsize 10M;
Remember to change the datafile name to the name of to suit your filesystem!
Your should see the result.
Tablespace altered.
The line 'next 100K' specifies the increments that the datafile will be enlarged by. The line 'maxsize 10M' specifies the maximum size the datafile will be allowed to grow to.
Hint: If you are going to autoextend datafiles then you should ensure that you have set a maxsize for them. Otherwise you could find your hard disk full one day without realizing it!
When dealing with temporary tablespaces the only difference is that you don't add datafiles but tempfiles!
Good DBA'ing!