datafile rsize add? 1

[watergrinder]

Generally which is the best way? To resize a datafile or add new datafile? The datafile that we have is 8.3 GB in size. The filesystem where the datafile resides is 54% free with 8 GB remaining space. So, should this datafile be resized OR should a new datafile be added? Thanks.

 

[SantaMufasa]

Grinder,

Since SOME operating systems have problems with data files that exceed 2GB, we generally cause our datafiles to autoextend to "maxsize 2000m". Adding datafiles is certainly not a problem. Here is the code I use to add datafiles to a tablespace:

ALTER TABLESPACE yada DATAFILE '/path/path/file.ext' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:52 (08May04) UTC (aka "GMT" and "Zulu"), 22:52 (07May04) Mountain Time)

 

[watergrinder]

Is there a difference between the below methods with regards to speed, size, etc....

a) SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;
b) SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;
c) A constant 2000M size?

 

[SantaMufasa]

Grinder,

Any of the three options you list are acceptable. Their effects are different in the following ways:

1) Starts at 10MB and grows in 10MB increments to 2GB.
2) Starts at 100MB and grows in 100MB increments to 2GB.
3) Starts life at 2GB and stays at that size.

In terms of performance, option 3 least impacts performance since it allocates disk space once. Option 2 allocates disk space potentially 20 times, and option 1 allocates potentially 200 times. In the grand scheme of things, option 1 isn't even that much of a performance hit.

The reason I choose option 1 is because we often have multiple tablespaces vying for space on the same file system, and I want whoever needs the space first to get the space. Therefore, I use "just-in-time" disk allocation; I do not like to pre-allocate space that Oracle might not use/need for several weeks.

As an example of worst-case scenario, a former DBA at one of my sites had the habit of pre-allocating 2GB of space for every tablespace. Following an audit, we discovered that some of the 2GB tablespaces had been consuming less than 20MB of space for months, totalling hundreds of GB of dormant space. The IT Department had continued to purchase additional disk space to feed the "Oracle monster". Once we reconfigured the tablespaces for "just-in-time" allocation, IT did not need to purchase more disk space for the ensuing 18 months !

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:43 (10May04) UTC (aka "GMT" and "Zulu"), 13:43 (10May04) Mountain Time)