RE: datafiles space allocation algorithm

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Fri, 6 Oct 2006 13:41:49 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF615DD3@MSXVS04.trivadis.com>

Kurt

> This might be true on dictionary managed tablespaces and LMT Uniform
> size, but not so for LMT Autoallocate :

With autoallocate it seams that the size is used instead of the number of extents...

In the following example one MB for each datafile:

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 autoallocate;

SQL> create table t (n number)
  2 tablespace t;

SQL> begin
  2 for i in 1..30 loop
  3 execute immediate 'alter table t allocate extent';   4 end loop;
  5 end;
  6 /

SQL> select file_id, extent_id, bytes
  2 from dba_extents
  3 where owner = user and segment_name = 'T   4 order by extent_id;

   FILE_ID EXTENT_ID BYTES
---------- ---------- ----------

         6          0      65536
         6          1      65536
         6          2      65536
         6          3      65536
         6          4      65536
         6          5      65536
         6          6      65536
         6          7      65536
         6          8      65536
         6          9      65536
         6         10      65536
         6         11      65536
         6         12      65536
         6         13      65536
         6         14      65536
         6         15      65536
         7         16    1048576
         8         17    1048576
         5         18    1048576
         6         19    1048576
         7         20    1048576
         8         21    1048576
         5         22    1048576
         6         23    1048576
         7         24    1048576
         8         25    1048576
         5         26    1048576
         6         27    1048576
         7         28    1048576
         8         29    1048576
         5         30    1048576

SQL> select 65536*16 from dual

  65536*16

---

   1048576

Cheers,
Chris

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

Received on Fri Oct 06 2006 - 06:41:49 CDT