Showing results for 
Search instead for 
Did you mean: 

manage tablespace

Go to solution
Frequent Advisor

manage tablespace


I would like ask a simple question :
some tablespace on db are full so I have to increase the size with the instruction
DATAFILE '...../users01.dbf' RESIZE
or the same with the tool oracle enterprise manager, or with the follow instruction:
'...../users02.dbf' SIZE 200M

1) in which case is better add another datafile
instead of increase the existing ?
2) is possible to increase it on line, while users are working on it ?

Piergiacomo Perini
Trusted Contributor

Re: manage tablespace

Hi Dartan,

your question it's a good question.
By my side, I always use "alter tablespace ... add datafile..." (especially on unix server) so i can decide if autoextend or not (i use don't extend) and use other option.


Hein van den Heuvel
Honored Contributor

Re: manage tablespace

>> 1) in which case is better add another datafile instead of increase the existing ?

Better for who ot what?
Performance? Simplicity ? Backups ?
- extending an existing datafile keeps it clean and simple. Nothing new to deal with when using OS tools for the DB files. When using Oracle tools the datafiles are pretty much transparent.
- adding a datafile gives you the option to pick a new mountpoint which in turn may give you more space to grow, better/more access paths to the IO subsystem. The relevance of this depends on exct details of the storage/hpux configuration.
- adding a datafile may be needed if you have a datafile size limitation (a real limitation, or just a policy perhpas)

>> 2) is possible to increase it on line, while users are working on it ?

Adding a datafile can be done online for sure. According to the manual reszing is also done online but I never tried that. Read the manual! Read the other references:
google: +"alter datafile" +resize

btw... this question, and any follow up questions are probaly better asked to the Oracle community, as there is little or no hpux aspect to it.

Hein van den Heuvel
HvdH Perfomance Consulting

Honored Contributor

Re: manage tablespace

It's not a hard and fast rule - as you can get the idea from the other folks. It depends what you're up and what your goals are for the next level of data allocated in your system.

I like to spread the load around in my very busiest database files - so they are likely to travel all over the database landing spots. But the smaller modules that don't grow and just need a little more space, I generally just resize more space right where they are.

Keep in mind that as you increase the number of datafiles, the number of open files on the server at once go up dramatically (each connection to the database can and will open a number of files itself). So it's important to keep an eye on all of various file limiting kernel parameters and make sure you don't go over (nfile, maxfiles, maxfiles_lim).

It's also a good time to take advantage of some abilities to increase your performance.

If this is a fast growing data area - I will often use the opportunity to not just add one file, add 5 or 6 datafiles across several mount points, and let my data stripe across quite a bit of hardware from my storage arrays.

Also, practically speaking, unless my agenda
is going for something more or different - I often let my files max out at 2G (1999M actually) - this is just an old habit from way back when I had noticed that lots of things seem to not like files over that size, and not just the file system itself. You can (and many folks do) however, run fine with larger files - it's just my preference.

Re: autoextend - you'll catch a lot of flack on this for leaving it turned on. I too leave it on ( and at times catch flack - mainly in this forum ... :-) ), but I never use it as the main source of data growth. One problem (there are more) is that it really leaves your big data files pretty well cut up over the file system space, especially if you have many files all taking their turn, each grabbing the next 10M or so. If you've got lots of tablespaces, it can get pretty ugly in there. I prefer to use it as a fail-safe mechanism, I routinely go over my file systems and look for things about to go over their file space limitations, and manuall intervene in various ways ( but certiainly not in little 10M chunks). And, in case I miss it - I have cron jobs that watch the alert file and scan for autoextending activity - which email and let me know that I need to jump in and check out the growth and intervene. Which all just says, don't let autoextend play even much of a minor role in your storage strategy. Of course, since you're asking about autoextending, I'm thinking that was probably your plan all along anyway. The point is, of course, just be careful with autoextend.

Good luck.
We are the people our parents warned us about --Jimmy Buffett
Bill Davison
Occasional Contributor

Re: manage tablespace

We're in a SAN environment, so not overly concerned with spreading the I/O around (hard to get good spindle-level info from the SAN team in any case).

Our first thought was - lets go with fewer, larger files to make thinks simpler. Oracle takes this idea to the extreme in 10g with "bigfile tablespaces", where you can have a max of 1 datafile per tablespace, and that one file can be as large as 128 terabytes(!).

Anyway, our next thought was - what will recovery time look like if we have to restore a big datafile from a backup. Based on that concern, we decided to increase a datafile up to a max of 10GB, then add a new datafile. A neighboring DBA team decided they were comfortable at 25GB per datafile.

I resize datafiles and add new datafiles every day on busy production systems. No problem with it whatsoever.

Side note - after you add a datafile it's good practice to immediately update your controlfile backup (alter system backup controlfile to trace;). That'll make your life easier if you get into a recovery situation before your next regular backup occurs.

Hope that helps
Honored Contributor

Re: manage tablespace


Also note that with ASM, DBAs no longer need to manage files and drives individually. Instead, disk groups can be created consisting of disks and their assigned files. Essentially, the combination of OMF and ASM eliminates the need for a DBA to specify the file name and location for the physical database files when creating a new database, as well as in other database operations - you simply identify the destination disk group, and oracle takes care of the rest.

ASM can also be extended to support other administrative procedures, including backup/recovery and disk management.

hope this helps too!

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