cancel
Showing results for 
Search instead for 
Did you mean: 

increase database size

Pieter_5
Advisor

increase database size

Hi,

My customer asks me to increase the size of a oracle database. How can i do this? Increase the tablespace which holds the data?
7 REPLIES
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

e.g.
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'filename1' SIZE 200M;

Rgds,
JL
fiat lux
Steve Steel
Honored Contributor

Re: increase database size

Hi

http://tahiti.oracle.com/

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.


Pete


Pete
twang
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?
or
Increase Datafiles space?

What you want to do increase?


GSM
Ghulam
Indira Aramandla
Honored Contributor

Re: increase database size

Hi,

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;

or

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.

IA
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: increase database size

hi,

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!

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