RE: datafiles space allocation algorithm

From: Kurt Van Meerbeeck <kurtvm_at_pandora.be>
Date: Fri, 06 Oct 2006 12:37:43 +0200
Message-Id: <4.1.20061006123135.012c2e80@in.pandora.be>

This might be true on dictionary managed tablespaces and LMT Uniform size, but not so for LMT Autoallocate :
SQL> create tablespace koert extent management local autoallocate datafile 'c:\d
1.dbf' size 10M, 'c:\d2.dbf' size 10M, 'c:\d3.dbf' size 10M, 'c:\d4.dbf' size 10
M ;
Tablespace created.
SQL> select file_id, file_name from dba_data_files where tablespace_name='KOERT' ;
FILE_ID

---

FILE_NAME

---

---

7
C:\D1.DBF
8
C:\D2.DBF
9
C:\D3.DBF
10
C:\D4.DBF SQL> create table t (n number) tablespace koert ; Table created.
SQL> insert into t select object_id from dba_objects ; 44129 rows created.
SQL> commit ;
Commit complete.
SQL> select file_id, extent_id from dba_extents where owner='SYS' and segment_name='T' ;
FILE_ID EXTENT_ID
---------- ----------
10 0
10 1
10 2
10 3
10 4
10 5
10 6
10 7
10 8
9 rows selected.

cheers,
Kurt

At 11:59 6/10/2006 +0200, Christian Antognini 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
>
>

-- 
Kurt Van Meerbeeck
kurtvm_at_pandora.be
kurt_van_meerbeeck_at_axi.be
dude_at_ora600.org
http://www.ora600.org

Imagination is more important than knowledge... (A.Einstein)

You can have many different jobs and still be lazy... (H.Simpson)




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

Received on Fri Oct 06 2006 - 05:37:43 CDT