something about initial parameter int LMT.

initial and lmt - 8i  July 09, 2003
Reviewer:  M. Bento  from Lisbon, Portugal

When moving a table into a uniform LMT using alter table without the storage
clause the table's initial is not set to the LMT default initial parameter but
keeps its previous value ending up with tables with different initial extents
inside the same LM tablespace.
Should one set the initial along with the alter table in order to prevent
fragmentation?
Thanks.


Followup:  
you are confusing the initial extent sizes with actual extent sizes.

Rest assured, the EXTENTS actually allocated are all the same size in that
UNIFORM LMT -- use user_extents to verify that.

The INITIAL EXTENT (and next extent and pctincrease and minextents) are used
during the initial creation to figure out HOW MANY extents to create.  Consider:

ops$tkyte@ORA920> create tablespace testing
  2  extent management local
  3  uniform size 512k
  4  /

Tablespace created.

ops$tkyte@ORA920> compute sum of mbytes on report
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x int )
  2  tablespace testing
  3  storage ( initial 1m );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select extent_id, bytes/1024/1024 mbytes
  2    from user_extents
  3   where segment_name = 'T'
  4   order by extent_id;

 EXTENT_ID     MBYTES
---------- ----------
         0         .5
         1         .5
           ----------
sum                 1

see, 2 extents of 512k -- for a total of 1m, because the 1m was requested..

ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t;

Table dropped.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x int )
  2  tablespace testing
  3  storage ( initial 1m next 1m minextents 4 pctincrease 100 );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select extent_id, bytes/1024/1024 mbytes
  2    from user_extents
  3   where segment_name = 'T'
  4   order by extent_id;

 EXTENT_ID     MBYTES
---------- ----------
         0         .5
         1         .5
         2         .5
         3         .5
         4         .5
         5         .5
         6         .5
         7         .5
         8         .5
         9         .5
        10         .5
        11         .5
        12         .5
        13         .5
        14         .5
        15         .5
           ----------
sum                 8

16 rows selected.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select 1 + 1 + 2 + 4 from dual;

   1+1+2+4
----------
         8

there, Oracle took the initial, next, minextents and pctincrease and computed
"hey, it would have taken 8m in a DMT, please grab 8m of space" and did so, 512k
at a time...


ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t;

Table dropped.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x int )
  2  tablespace testing
  3  storage ( initial 64k minextents 10 );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select extent_id, bytes/1024/1024 mbytes
  2    from user_extents
  3   where segment_name = 'T'
  4   order by extent_id;

 EXTENT_ID     MBYTES
---------- ----------
         0         .5
         1         .5
         2         .5
         3         .5
         4         .5
         5         .5
         6         .5
         7         .5
         8         .5
         9         .5
           ----------
sum                 5

10 rows selected.

ops$tkyte@ORA920>
ops$tkyte@ORA920>

Here Oracle did 64k * 10 = 640k, but also saw the minextents 10 -- and said,
allocate at least 10 extents, as long as 10 extents of space is larger then
640k

But there is NO fragmentation here, they are all 512k extents.
 
 [@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-796098/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-796098/