RE: LMT Autoallocate initial, tot # of extents

>-----Original Message-----
>Behalf Of Jared Still
>
>Setting 'INITIAL' will not affect the size of the extents IIRC, just
>the number of them.

Setting initial will affect the size of the extents in an autoallocate tablespace. If you have a large INITIAL then the first extent sizes will not start off at 64K but will "jump" to the appropriate size as determined by the internal algorithm. Example:

SQL> select tablespace_name, block_size, extent_management, allocation_type   2 from dba_tablespaces
  3 where tablespace_name = 'TRUCS' ;

TABLESPACE_NAME                BLOCK_SIZE EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------- ---------
TRUCS                                4096 LOCAL      SYSTEM

SQL> create table t (n number) tablespace trucs storage (initial 1M) ; Table créée.
SQL> select bytes, count (*) from user_extents   2 where segment_name = 'T'
  3 group by bytes ;
    BYTES COUNT(*)
--------- ---------

    65536 16

SQL> drop table t ;
Table supprimée.
SQL> create table t (n number) tablespace trucs storage (initial 100M) ; Table créée.
SQL> select bytes, count (*) from user_extents   2 where segment_name = 'T'
  3 group by bytes ;
    BYTES COUNT(*)
--------- ---------

  1048576        49
  8388608         7

SQL> drop table t ;
Table supprimée.
SQL> create table t (n number) tablespace trucs storage (initial 1500 M) ; Table créée.
SQL> select bytes, count (*) from user_extents   2 where segment_name = 'T'
  3 group by bytes ;
    BYTES COUNT(*)
--------- ---------

  8388608       113
 67108864        10

SQL>

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