If I have 2 datafiles attached to a tablespace, and BOTH are set to AUTOEXTEND and BOTH are set to unlimited, will Oracle know to extend both datafiles, or only extend one of them. I have read through many manuals, but none of them answer this question. As to why this is set like this, well, it's an inherited system that I am starting to tune.
A:
It depends on how much disk space you have (among other things). "Unlimited" in this context means "to the largest supported table size on your platform" not "grab up all the disk space even if you can't use it." If you have enough disk space for two maximum-sized datafiles, you should be fine.
Also (possibly dumb question) since this is an existing application, why can't you just look and see what it did?
-- MarkusQ
MarkusQ
2009-02-25 18:31:43
I setup a little experiment, and it seems that it only extends the 2nd datafile. There wasn't enough data generated by the application to fill up even one of the datafiles, that's why I've just setup this experiment. I think that I should take the unlimited options off.
Roqetman
2009-02-25 18:56:04
A:
If you have 2 datafiles in the same tablespace, oracle will only fill/extend the 2nd, when the first one is filled to its maxsize.
If you have unlimited on the first, there will be no reason (at least that I can think of right now) when oracle itself will fill/use/extend the 2nd datafile.
Even if you have run out of space on the first Datafile's filesystem/ASM/whatever, I'm quite sure, Oracle will still try to extend the 1st datafile (At least that's what I think I saw just a few hours ago :P).
Please anyone, correct me if I'm wrong.