New Extent Allocation

[Alter]

Hello

Is there a way to prevent Oracle from allocation new extents in all datafiles belong to tablespace?

For example I have three datafiles in tablespace. When Oracle allocate first extent it allocate it in Datafile 1, next - in Datafile 2, third - in Datafile 3 and fourth - again in Datafile 1.

How can I make so, that Oracle will start to allocate extents in Datafile2 only when Datafile1 is full i.e. allocation extents in next datafile performs only when previous datafile was over.

Thanks

 

[alexhu]

I believe that this is how Oracle is supposed to work. If you really want to change it how about:

Shrink datafile 1 to minimum when it is almost full.
At the same time shrink datafile 3 to minimum

All new extents will go into datafile 2 (unless there are free extents in datafile 1

When datafile2 is nearly full shrink it to minimum and expand daatfile 3 and so on .......

Sounds like a lot of effort for not much result !!

Alex

 

[rcurva]

You can preallocate your extents to those datafiles;

ALTER TABLE tab1
ALLOCATE EXTENT (DATAFILE 'datafile1');

ALTER TABLE tab1
ALLOCATE EXTENT (DATAFILE 'datafile2');

ALTER TABLE tab1
ALLOCATE EXTENT (DATAFILE 'datafile3');

 

[alexhu]

Good point 'rcurva' but this could be a real pain to manage if 'Alter' has 600 tables in his tablespace

Alex

 

[rcurva]

You can preallocate your tables to one datafile if you want to, but this will be a lot of work. But performance wise, it is better to have distributed data (striped) across datafiles.