Understanding Oracle's Locally Managed Tablespaces - Page 2

June 23, 2003

Storage parameters usage in LMT

Storage parameters are used in DMTs to specify the object sizing. These parameters are not of much importance in UNIFORM type LMTs but play a role in deciding the initial allocation of space. Oracle considers the storage clause for the initial number of extents that should be allocated. For example, LMT is created with 32K extent size. The database block size is 8k.

SQL> create table am05 (col1 number)
  2  storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);

SQL> select segment_name, segment_type, extent_id,  bytes, blocks
  2  from user_extents where segment_name = 'AM05';

SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
-------------------- ------------------ ---------- ---------- ----------
AM05                 TABLE                       0      32768          4
AM05                 TABLE                       1      32768          4
AM05                 TABLE                       2      32768          4
AM05                 TABLE                       3      32768          4

Oracle allocates four extents, the total size being 128K that is closer to the 100K provided for initial extent size. Please note that all the extents allocated have the uniform extent size of 32K. Only the number of extents to be allocated is decided based on the storage clause. See example below to clarify this.

SQL> create table am06 (col1 number)
  2  storage(initial 200k next 100k minextents 2 maxextents unlimited pctincrease 0);

SQL> select segment_name, segment_type, extent_id,  bytes, blocks
  2  from user_extents where segment_name = 'AM06';

SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
-------------------- ------------------ ---------- ---------- ----------
AM06                 TABLE                       0      32768          4
AM06                 TABLE                       1      32768          4
AM06                 TABLE                       2      32768          4
AM06                 TABLE                       3      32768          4
AM06                 TABLE                       4      32768          4
AM06                 TABLE                       5      32768          4
AM06                 TABLE                       6      32768          4
AM06                 TABLE                       7      32768          4
AM06                 TABLE                       8      32768          4
AM06                 TABLE                       9      32768          4

10 rows selected.

SQL> select sum(bytes)/1024 from  user_extents where segment_name = 'AM06';

SUM(BYTES)/1024
---------------
            320

As per the storage clause, the table should be allocated 200K + 100K of space (since minextents is 2). Oracle rounds off on the higher side and allocates 10 extents of 32K, totaling 320K.

Even pctincrease plays a role in uniform LMTs as the below example shows.

SQL> create table am07  (col1 varchar2(200))
  2  storage(initial 16K next 16K minextents 5 maxextents unlimited pctincrease 50);

Table created.

SQL> select segment_name, segment_type, extent_id,  bytes, blocks
  2  from user_extents where segment_name = 'AM07';

SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
-------------------- ------------------ ---------- ---------- ----------
AM07                 TABLE                       0      32768          4
AM07                 TABLE                       1      32768          4
AM07                 TABLE                       2      32768          4
AM07                 TABLE                       3      32768          4
AM07                 TABLE                       4      32768          4

SQL> select sum(bytes)/1024 from  user_extents where segment_name = 'AM07';

SUM(BYTES)/1024
---------------
            160

As per the storage clause the required initial size of the table should be 146K (16 + 16 + 24 + 36 + 54), Oracle rounds on the higher side to 160K (5 32K extents).

Hence, storage could be used to allocate the initial size for an object. The Default Storage clause cannot be specified for LMTs at tablespace level.

SQL> create tablespace users4
  2  datafile 'D:\oracle\oradata3\users4.dfb' size 5M
  3  autoextend off
  4  extent management local uniform size 32K
  5  default storage(initial 100k next 100k minextents 2 maxextents unlimited pctincrease 50);
create tablespace users4
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

Please refer the example section for LMT creations and migration examples.