Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, vuppala .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: May 17, 2021 - 12:25 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

what is the way of changinng max size of a tablespace.
1.with out shut down of the database.
2.with shutdown of database.
waht is the same way.?


and Tom said...



sure -- in fact you cannot really change the size of a tablespace without the database being UP!

1) alter tablespace table_space_name add datafile '/path/to/some/file.dbf' size Xm;

that will add a new file to the existing tablespace. This file will be immediately available for use.

2) alter database datafile '/path/to/some/existing/file.dbf' resize Xm;

that will resize an existing file (can be used to shrink or grow files). You would select file_name from dba_data_files where tablespace_name = 'YOUR_TABLESPACE' to find the files associated with your tablespace and 'grow' one of them. I recommend never growing a file over 2gig.


3) alter database datafile '/path/to/some/existing/file.dbf' autoextend on next Xm maxsize Ym;

this lets the tablespace grow dynamically as it needs it. make sure you understand this one if you use it. Make sure to set a maxsize and try not to go over 2gig. Make the Xm be some reasonable number that fits in with the size of the extents this tablespace generates (eg: if you extents are 5m, don't use a next size of 4m as we will have to do 2 extends to get an extent and then have 3m left over).



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 09, 2002 - 2:21 am UTC


adding new datafile after 2 gb

OCT, December 23, 2002 - 10:45 pm UTC

Hi Tom
Thanks ,I have a further add on to this
we have two Tablespace Data and Index in LMT mode.
both have one datafile size 1.6 gb data and 1.8gb index on different HDD.
Now as you mentioned that "never growing a file over 2gig."
1. why?please brief.
2.what is the best choice when we add a new datafile to above tablespace:-1.on the same HDD as the old one
or on different HDD.
Our DBase growing at the rate of 5-6 mb /week.
Thanks


Tom Kyte
December 24, 2002 - 8:27 am UTC

1) i never say never - except to say that I never say never.

I said in most cases i PREFER to keep the file at 2gig or less as all OS utilities will work with 2gig and less files, some will not and not all file systems support files over 2gig.

However, if I'm working on a really big database -- hundreds of gigabytes -- large files will be something to consider.

2) you want to add the datafile to the drive that is currently experiencing the least amount of IO.

thanks

A reader, December 24, 2002 - 10:14 pm UTC


A reader, May 06, 2003 - 8:09 pm UTC

Currently I have only one datafile created in the tablespace. And created 1m size with the autoextend and maxsize 10G. I want to change the maxsize to be 5G. Is it possible?

Thanks

Mike

Tom Kyte
May 06, 2003 - 8:57 pm UTC

sure alter database datafile 'foo' autoextend on maxsize ...;


See this

V.Vijay Kaushik, April 01, 2004 - 6:02 am UTC

Hi,
You can change the size from 10G to 5G by the above mentioned command,but if your datafile already has datas exceeding 5G you will get error when you try to resize from 10G to 5G.Hope this helps.



Just a clarification

A reader, December 24, 2004 - 2:49 pm UTC

Can we use the ALTER DATABASE .. RESIZE command for datafiles belonging to SYSTEM tablespace. Shouldn't the database be brought down if it involves SYSTEM tablespace?
The database needs to be shutdown only when you want to rename a file belonging to the SYSTEM tablespace. Is that correct?
If you want to rename a datafile belonging to a tablespace other than SYSTEM tablespace, it is enough if the tablespace is brought offline and then brought back online after the file is renamed. Is that correct? If not please advise

Tom Kyte
December 24, 2004 - 3:25 pm UTC

no -- resizing is just fine.


you need to shutdown to rename (move) the system datafiles, but not to resize them (they grow as needed with autoextend as well).

you only need to offline a tablespace other than system to rename its files.

datafile size

reader, December 24, 2004 - 8:15 pm UTC

From manageability point of view, why would DBAs prefer to use 10g Big File tablespace? If the file grows to 20G in a bigfile tablespace for example, and if I loose that file for some reason, I have to restore the entire file from backup and my whole tablespace(application) is offline until the recovery is complete. Thanks.

Tom Kyte
December 24, 2004 - 9:52 pm UTC

you've had block level recovery (you know, 2k at a time if that is what you are using) since 9i.

the tablespace hasn't been the unit of recovery for years.

Muhammd Harshad, March 23, 2005 - 7:49 am UTC

Thanks Tom,

I was totally desperated , without this help, now My application is running by executing your commands,

Thanks again really really good


Enterprise Edition

Jack Douglas, February 06, 2006 - 1:55 pm UTC

Tom said: "you've had block level recovery (you know, 2k at a time if that is what you are using) since 9i."

For anyone not running Enterprise Edition you will need to be aware that this feature is not available. I think this means you might well want to consider keeping the maximum file size of datafiles down in order to keep MTTR down.

Please correct me if I'm wrong here.

Jack

Tom Kyte
February 07, 2006 - 1:09 am UTC

if your unit of recovery is a file, then yes, the size of the file will be one of the major factors in determing the time to repair (however, the time to get a single file of any size off of a sequential IO device like a simple tape drive is probably the largest and if you are small enough to be using SE, you are likely using a relatively inexpensive tape unit as well - so the time to repair is likely less a function of the file size and mostly a function of how fast your tape drive can seek)

DBA

Bate A Obennjock, May 17, 2021 - 2:20 am UTC

I added datafile but is still showing 100 percent full. I not using the maxsize i don't know why.
Chris Saxon
May 17, 2021 - 12:25 pm UTC

What's showing 100% full?