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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

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).