Re: datafiles space allocation algorithm

Hi Chris

This is true for the straightforward case of one table on its own in its own ts but is affected by factors such as

other objects also growing at the same time files having autoextend or not - especially if there is a mix in the same ts available 'holes' in the existing datafiles

and so on.

I had a script that demonstrated some of this a while back, I'll see if I can dig it up.

In general though I can't really see why, other than curiosity, anyone would care about where a next extent would be allocated these days. (just that it would be).

On 10/6/06, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
>
> Alex
>
> > Suppose we have 4 datafiles in a tablespace, when we load data into
> > this tablespace how will Oracle assign the space? I thought it would
> > use round-robin like but it does not.
>
> Extents allocation is performed round-robin for a segment. Here an
> example...
>
> SQL> create tablespace t
> 2 datafile '/tmp/t1.dbf' size 10m,
> 3 '/tmp/t2.dbf' size 10m,
> 4 '/tmp/t3.dbf' size 10m,
> 5 '/tmp/t4.dbf' size 10m
> 6 extent management local uniform size 1m;
>
> SQL> select file_id, file_name
> 2 from dba_data_files
> 3 where tablespace_name = 'T';
>
> FILE_ID FILE_NAME
> ---------- ----------------------------------------
> 5 /tmp/t1.dbf
> 6 /tmp/t2.dbf
> 7 /tmp/t3.dbf
> 8 /tmp/t4.dbf
>
> SQL> create table t (n number)
> 2 tablespace t
> 3 storage (initial 20m);
>
> SQL> select file_id, extent_id
> 2 from dba_extents
> 3 where owner = user and segment_name = 'T'
> 4 order by extent_id;
>
> FILE_ID EXTENT_ID
> ---------- ----------
> 6 0
> 7 1
> 8 2
> 5 3
> 6 4
> 7 5
> 8 6
> 5 7
> 6 8
> 7 9
> 8 10
> 5 11
> 6 12
> 7 13
> 8 14
> 5 15
> 6 16
> 7 17
> 8 18
> 5 19
>
> SQL> drop tablespace t including contents and datafiles;
>
>
>
> HTH
> Chris
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l