Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 23, 2003

Understanding Oracle's Locally Managed Tablespaces - Page 2

By Amar Kumar Padhi

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM