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.
Previous |
Next
Prev [1] [2] [3] [4] Next
