RE: datafiles space allocation algorithm
- From: Kurt Van Meerbeeck <kurtvm@xxxxxxxxxx>
- To: Christian.Antognini@xxxxxxxxxxxx, <ax.mount@xxxxxxxxx>
- Date: Fri, 06 Oct 2006 12:37:43 +0200
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
>--
>//www.freelists.org/webpage/oracle-l
>
>
--
Kurt Van Meerbeeck
kurtvm@xxxxxxxxxx
kurt_van_meerbeeck@xxxxxx
dude@xxxxxxxxxx
http://www.ora600.org
Imagination is more important than knowledge... (A.Einstein)
You can have many different jobs and still be lazy... (H.Simpson)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
