[Translated from mos] Why does the INITIAL parameter be ignored when a table is created in a tablespace locally managed using the uniform size ?, Mosuniform

[Translated from mos] Why does the INITIAL parameter be ignored when a table is created in a tablespace locally managed using the uniform size ?, Mosuniform

Translation: Why Does a Table Created in a Locally Managed Tablespace With Uniform Extents Ignore INITIAL? (Documentation ID 753662.1)

Why does the INITIAL parameter be ignored when creating a table in a locally managed tablespace using the uniform size?

Applicable:
Oracle Database-Enterprise Edition-Version 8.1.5.0 to 11.1.0.7 [Release 8.1.5 to 11.1]
Information in this document applies to any platform.
Information in this document applies to any platform.

Solution:

When creating a table in a tablespace managed locally using the uniform size, the initial extent size specified during table creation will be partially used (are only partially used)
There are three situations:

Example:

create tablespace testdatafile '/u01/app/oracle/oradata/KBCOOK/test01.dbf' size 50mextent management local uniform size 1m;

This tablespace applies to the following three examples.

1) if initial extent is smaller than the UNIFORM size, the UNIFORM size of MINEXTENTS is created (see the Case below)

SQL> create table test_tab (col1 varchar2(2)) tablespace test storage(initial 65535 minextents 1); SQL> select bytes from dba_extents where segment_name = 'TEST_TAB' and owner = 'KBCOOK'; BYTES ---------- 1048576 1 rows selected. 

Note: If the initial extent is smaller than the UNIFORM size, then the MINEXTENTS number of extents are created

In fact, I think then the MINEXTENTS number of extents are created is not clear. the literal translation is: then the number of MINEXTENTS is created. The number is there, but no one says the number. Based on the preceding SQL query results, it is assumed that the number of MINEXTENTS UNIFORM size extents is created.

For this I did an experiment, see the following article: http://blog.csdn.net/msdnchina/article/details/43836519

This experiment confirms the above assumption that, if the initial extent is smaller than the UNIFORM size, the extents of the number of minextents uniform size will be created.

2) if initial extent is greater than or equal to UNIFORM size but less than UNIFORM size multiplied by MINEXTENTS, the extent of minextents uniform size will be created (see the Case below)

SQL> create table test_tab (col1 varchar2(2)) tablespace test storage(initial 1m minextents 5); Table created. SQL> select bytes from dba_extents where segment_name = 'TEST_TAB' and owner = 'KBCOOK'; BYTES ---------- 1048576 1048576 1048576 1048576 1048576 5 rows selected. 

In this example, INITIAL = the UNIFORM size, then five (MINEXTENTS value) UNIFORM size extent is created.

Original article: In this example, our INITIAL (1 mb) is equal to the UNIFORM size and MINEXTENTS (5) will produce five 1 mb extents.

3) if initial extent is greater than UNIFORM size multiplied by MINEXTENTS, the minimum number of UNIFORM extents is created to meet the storage requirements of initial extent. (See the Case below)
Original article:
If the initial extent is larger than the UNIFORM size times MINEXTENTS then the minimum number of UNIFORM extents will be created to meet the storage of the initial extent (see Case 3 below)

SQL> create table test_tab (col1 varchar2(2)) tablespace test storage(initial 5m minextents 2); SQL> select bytes from dba_extents where segment_name = 'TEST_TAB' and owner = 'KBCOOK'; BYTES ---------- 1048576 1048576 1048576 1048576 1048576 1048576 6 rows selected. 

In this example, our first five 1 mb extents are the initial extent the sixth 1 mb extent is the second extent of our MINEXTENTS = 2