2 Datafiles in 1 Tablespace

[iopunx02]

I just want to clarify things about the following matters:

1) Is it possible to have two datafiles in one tablespace where one of the datafiles in Autoextensible while the other one is not.

2) If #1 is ppossible, how would the datafiles behave. Does this mean that the datafile which is autoextended will just continue to grow and the other datafile will never be used.

Your inputs will be highly appreciated.

[gopu_g]

Yes, you can add more than one datafiles to a tablespace, you can make one datafile autoextend on and the other off...

for example

ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;

The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

disables the automatic extension for the datafile.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;

[PAVB]

Answering your questions...

1) Yes.
2) Not necessarily. It dependes of Oracle determination of the candidate datafile that happens each time space has to be allocated. Last time I checked the "candidate datafile determining" algorithm was propietary -meaning is not published - but testing suggested it was sort of an altered round-robin process... being the "altered" part the unpublished one. By the way, if somebody out there has specifics on this matter it would be nice to hear from you.

[iopunx02]

Answering your questions...

1) Yes.
2) Not necessarily. It dependes of Oracle determination of the candidate datafile that happens each time space has to be allocated. Last time I checked the "candidate datafile determining" algorithm was propietary -meaning is not published - but testing suggested it was sort of an altered round-robin process... being the "altered" part the unpublished one. By the way, if somebody out there has specifics on this matter it would be nice to hear from you.

Does this mean that you do not have control of whether data will be put to Datafile1 (autoextended datafile) or Datafile2 (not autoextended)? So it could happen that even though Datafile1 is not yet full, there is still the possibility that it will already start writing on Datafile2?

[PAVB]

Exactly, that's the beauty of it.

If you want to have control over where data is written you create just one non-extensible datafile then when this one is close to be full you add a new datafile to the tablespace...
OR
...you go back in time to VSAM architecture

[iopunx02]

Exactly, that's the beauty of it.

If you want to have control over where data is written you create just one non-extensible datafile then when this one is close to be full you add a new datafile to the tablespace...
OR
...you go back in time to VSAM architecture

Thanks for the input. Just wanted to clarify how datafiles behave... Anyway, after all, the main concern with database administration is to have enough space readily available to hold data. I just came across this issue when our senior DBA asked me to act on a tablespace which is getting full. I examined the datafile and found out that is autoextended. He insist to add another datafile. But as far as I am concerned, there's no need to worry since the existing datafile is already autoextended and will continue to grow if space is needed.

[PAVB]

...after all, the main concern with database administration is to have enough space readily available to hold data.

In my personal opinion backup/recovery strategy should be a higher concern.