Showing results for 
Search instead for 
Did you mean: 

increase database size


increase database size


My customer asks me to increase the size of a oracle database. How can i do this? Increase the tablespace which holds the data?
Jean-Luc Oudart
Honored Contributor

Re: increase database size

You can either increase a datafile size or add new datafile to the tablespace.

Check for large file options if your datafile is to exceed 2Gb

ADD DATAFILE 'filename1' SIZE 200M;

fiat lux
Steve Steel
Honored Contributor

Re: increase database size


Select what you want and search

You do need to register for this one but it is good

This is a good oracle site for documentation then you can see for yourself

Steve STeel
If you want truly to understand something, try to change it. (Kurt Lewin)
Pete Randall
Outstanding Contributor

Re: increase database size

I think the most appropriate course of action would be to ask your customer what they mean. This could be anything from increasing a tablespace to adding disk capacity. I think you need to narrow down the scope of the question.


Honored Contributor

Re: increase database size

To resize a datafile using the alter tablespace command similar to the following:
SQL> alter tablespace ABC datafile '/u00/oradata/ABC01.dbf' resize 800M;
Or adding a datafile to a tablespace:
SQL> alter tablespace ABC add datafile '/u00/oradata/ABC02.dbf' size 300M autoextend off REUSE;

Re: increase database size

Increase Database size?
There are two things.

Incearse Memory?
Increase Datafiles space?

What you want to do increase?

Indira Aramandla
Honored Contributor

Re: increase database size


When your customer says increase the database, they have to be more specific.

What is it they wanted to eb increased.

1. Is it a tablespace (datafile) to be increased. If so then you have to verify the following before you increase the datafile.
1.1 Check for available disk space.
1.2 Check the current size of the datafile. If you datafile is colse to 2 GB, then verify if your file system is defined for largefiles (> 2GB).
1.3 Then you can increase your datafile as

Alter database datafile '' resize ???M;


you can add a datafile to an existing tablespace as

Alter tablespace add datafile '' size ??M;

2. IF it was not to do with increasing the datafile size then may be increasing the storage parameters of a tablespace like (extent size (next) or maximum number of extents (maxextents).
This can be done by
Alter tablespace storage (next ??M maxextents 10240000);

3. If it is not the above two then may be adding a new redo log file or group.

4. It could be increasing the storage parameters of a particular table /index. This can be done as
Alter table storage (next ??M maxextents 10240000);

So find out what exactly is required and then apply the appropriate option.

Never give up, Keep Trying
Honored Contributor

Re: increase database size


If you are using Locally managed tablespaces (LMT), you should not be really concerned about "increasing the database size".

With the LMT and uniform extent sizes -- every extent will be exactly the same size. On top of that, configure your tablespace datafiles to autoextend to a defined MAXSIZE.

Hence, the size of the database should not be an issue (but available disk space on the system will be one!)

Maybe you are anticipating a growth in the number of users, processing, etc...

... then the appropriate action should be taken in reviewing the size of the SGA, rollback segments and temporary tablespace amongst others...

hope this helps!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)