SEO

#1 (**permalink**) 02-24-2008, 03:58 PM

Hi,
I need to increase the tablespace size for SYSTEM/TEMP and user defined
tablesspaces. I have 2 options
1) Adding one more datafile to tablespace with ALTER TABLESPACE command
2) Increasing size of existing database file size with ALTER DATABASE
command.

which option is better?

Is it possible to run these commands when database is open.

Thanks

#2 (**permalink**) 02-24-2008, 03:58 PM

It depends on a few things ...
first: what size are they currently (system 500m - 2gigs is ok by me,
and temp 4 - 16 gigs per file)
Next - what OS and what version of Oracle (size limitation...).
Also consider backup/recovery and administration of these files. For
example, it's nice to have several files making up the TEMP tablespace,
as it allows you to shrink the tablespace without dropping the
tablespace of modifying the users.
HTH

#3 (**permalink**) 02-24-2008, 03:58 PM

Can I perform resizing of datafile operation wher database is open?

#4 (**permalink**) 02-24-2008, 03:58 PM

Sorry - I missed that one,
YES - while the database is open.
As in Alter database datafile '.....' resize 210M;
Increasing the size is not a problem - even during other people 's
transactions.
Mind you if it is a production server, it might not be a good ideal,
but if you don't mind a blip in performance, it shoudn't be drastic.
Decreasing the size can be tricky, but has to do with the position of
the last used file block.

#5 (**permalink**) 02-24-2008, 03:58 PM

prashant.kodagali@patni.com wrote:
> Can I perform resizing of datafile operation wher database is open?

Syntax for these can be found at www.psoug.org
click on Morgan's Library
click on Data Files
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Increase tablespace size