RE: datafiles space allocation algorithm

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Fri, 6 Oct 2006 11:59:40 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF615DD0@MSXVS04.trivadis.com>

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

Received on Fri Oct 06 2006 - 04:59:40 CDT