RE: Extent allocation

Jeremiah is right. Depending on the version of the database this is an imaginary problem.
I have been using this 'auto round robin' feature since 8.0.6 (HP-UX 32-bit). Just did quite a bit of re-organization involving partitioning a large table and used this auto round robin extent allocation feature to spread out I/O...

Anyone seen this in any lower versions ??

Cheers!

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Jeremiah Wilton [SMTP:jwilton_at_speakeasy.net]
> Sent: Friday, July 20, 2001 4:57 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Extent allocation
>
> On Fri, 20 Jul 2001, Adrian Roe wrote:
>
> > Is there any way to get Oracle (816) to do round robin extent
> > allocation eg. if a tablespace has 4 data files and each file is on
> > a different disk, can extents be allocated from each file in
> > sequence ? As I understand, Oracle will fill one file and then go
> > onto the next file.
>
> It appears we may be discussing an imaginary problem. At least on the
> version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among
> files with available space automatically. It does not just fill up
> one datafile and move on to the next.
>
> I did a little experiment:
>
> SQL> create tablespace jeremiah_temp1
> 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m,
> 2 '/tmp/jeremiah_temp-02.dbf' size 10m,
> 3 '/tmp/jeremiah_temp-03.dbf' size 10m,
> 4 '/tmp/jeremiah_temp-04.dbf' size 10m;
>
> Tablespace created.
>
> SQL> create table foobar
> 1 (baz varchar2(10))
> 2 storage (initial 32k next 32k pctincrease 0 maxextents 4)
> 3 tablespace jeremiah_temp1;
>
> Table created.
>
> SQL> insert into foobar (baz)
> 1 select substr(trash,1,10) from garbage where rownum <= 6000;
>
> 6000 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select segment_name, e.bytes/1024 kb, file_name
> 1 from dba_extents e, dba_data_files d
> 2 where e.file_id = d.file_id
> 3 and e.segment_name = 'FOOBAR';
>
> SEGMENT_NAME KB FILE_NAME
> ------------------------------ ---------- ------------------------------
> FOOBAR 32 /tmp/jeremiah_temp-01.dbf
> FOOBAR 32 /tmp/jeremiah_temp-02.dbf
> FOOBAR 32 /tmp/jeremiah_temp-03.dbf
> FOOBAR 32 /tmp/jeremiah_temp-04.dbf
>
> So, it looks like it "round robins" automatically, and there is no
> need to do so manually. I don't know which version of Oracle was the
> first to do this.
>
> --
> Jeremiah Wilton
> http://www.speakeasy.net/~jwilton
>
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).