When I should I be adding another datafile ????

[Former Member]

Oracle Release 9.2.0.7.0 Production

OS: Microsoft Windows Server 2003 Enterprise Edition

When should I add a datafile ??????

I am running with autoextend on all my datafiles. As I understand it autoextend works like this:

If we create a data file "A" with a size of 10K and autoextend with increments of 10K and max size 10M.

Data file "A" can grow up to 10M, after which we would have to add a new data file. Also, autoextend extents within each datafile.

Now for Example as you can see below two of my tablespaces are 93.92% full and 99.77% full should

I be adding a datafile every time the %Used is over 90% used. PSAPDAP has a total of 6 datafiles and PSAPDAP640 has also a total of 6 datafiles

TABLESPACE Total(MB) Used(MB) Frees(MB)% % Used % Free

-

---

-

---

-

---

-

---

-

---

-

---

PSAPDAP 19180 18014 1166 93.92 6.08

PSAPDAP640 14200 14168 32 99.77 .23

Can someone please shed some light on how I can figure out when I should add another datafile to accomodate growth?? Is there an easy way to figure this out ????

Thank you for your time.

[Former Member · Accepted solution]

Hello,

I'd say that the answer depends a little on your policy.

I mean,

PSAPDAP640 seem to be pretty full.

32 Megs Free.

If it is a LMTS, that means that ( if all that free space is contiguous ) it would be not possible to add an extent for a big table ( probably you can check in DB02 the critical objects or if you run a DB check )

Now, depending on your policy, you can increase the size of the datafile(s) or increase the "MAXSIZE" of the datafile(s)

In Windows, SAP recommends a Maximum of 16 GB, there is a note, but I cannot find it now

You could go up to that limit.

But your policy could also limit the size of the datfiles to something smaller.

Also could be that you do not have "physical" space on the same location and the datafile cannot grow more. In that case the option is easy -> a new datafile.

Instead of looking at the % free I prefer to take a look to

1) free space in Mb.

10% free of 10 Gb tablespace is not the same as 10% free of 100Gb tablespace

2) fragmentation. Is all that "free" space contiguous or it is divided in multiple small chunks of contiguous space on different datafiles? You could have less "free" space than you think if you only look at the "global" number.

3) critical objects and their history

My two cents