STORAGE PRAMETERS AND SEGMENT SPACE MANAGEMENT IN ORACLE

STORAGE PARAMETERS IN ORACLE:-

• The storage_clause lets you specify how Oracle Database should store a database object.
• We also set in storage parameters to system and non-system tablespace.
• When you create a cluster, index, materialized view, materialized view log, rollback segment, table, or partition, 
•  you can specify values for the storage parameters for the segments allocated to these objects.

   

NOTE:-
If you  using locally managed tablespaces, you can omit these storage parameter when creating objects in those tablespaces.


Dictionary Managed Tablespaces:-

CREATE TABLESPACE tbsb DATAFILE '/home/oracle/icici/oradata/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY
   DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

INITIAL - Defines the size in bytes (K or M) of the first extent in the segment.

NEXT - Defines the size of the second extent in bytes (K or M)

PCTINCREASE - Specifies the percent by which each extent, after the second (NEXT) extent, grows

MINEXTENTS - Specifies the number of extents allocated when a segment is first created in the                                     tablespace

MAXEXTENTS - Determines the maximum number of extents that a segment can have. Can also be                                specified as UNLIMITED.

FREELIST - The block have free space and used space maintain in file header.

FREELIST - GROUP - Each free list group uses one database block.The default and minimum value                                         for this parameter is 1

OPTIMAL -  OPTIMAL keyword is relevant only to rollback segments. It specifies an optimal size                         in bytes for a rollback segment.

BUFFER_POOL -  BUFFER_POOL clause lets you specify a default buffer pool or cache for a                                         schema object.
                               All blocks for the object are stored in the specified cache.

To alter storage Attributes.

ALTER TABLESPACE users DEFAULT STORAGE (NEXT 100K MAXEXTENTS 20 PCTINCREASE 0);

Locally Managed tablespace:-

AUTOALLOCATE:

• The tablespace to be system managed with the smallest extent size being 64K.

CREATE TABLESPACE lmtbsb DATAFILE '/home/oracle/icici/oradata/data/lmtbsb01.dbf' SIZE 50M  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

UNIFORM:

• Then the tablespace is managed with uniform size extents of the specified SIZE. The default SIZE is 1M.

CREATE TABLESPACE lmtbsb DATAFILE '/home/oracle/icici/oradata/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

SEGMENT SPACE MANAGEMENT:-

1. Low High Water Mark (LHWM) - Like the old High Water Mark, all blocks below this point have already been formated for use.
2. High High Water Mark (HHWM) - Indicates the point above which no blocks have been formatted.

DMT SEGMENT SPACE MANAGEMENT:-

PCTFREE  -   The percentage of space in each data block of the database object reserved for future                              updates to rows of the object.
                       The values range  0 to 99. The default value is 10--10%

PCTUSED - The minimum percentage of used space that Oracle maintains for each data block of the                        database object.
                       The Values range from 0 to 99 and defaults to 40.
INITRANS - The number of concurrent transaction entries allocated within each data block allocated                       to the database object.
                      The values can range from 1 to 255 and defaults to 1.
MAXTRANS  - The parameter determined the maximum number of concurrent update transactions                                  allowed for each data block in the segment.
                        This parameter has been deprecated,Oracle now,automatically allows up to 255                                      concurrent update transactions for any data block.
 
CREATE TABLE TABLE1(id number)PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE    (INITIAL  64K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE  0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

LMT SEGMENT SPACE MANAGEMENT:-

MANUAL:-

•     MANUAL tells Oracle that you want to use free lists for managing free space within                   segments.
•    we need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS                storage  parameters for schema objects. 
•    MANUAL is the default.

Example:-
 create tablespace lmt_maual datafile '\home\Oracle\icici\Oradata\data\tsh1.dbf' SIZE 10M 
     extent management local segment space management manual;
  
AUTO:-

•    Oracle that you want to use bitmaps to manage the free space within segments.
•    It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and               FREELISTS GROUPS storage parameters.

Example:-
create tablesapce lmtauto datafile '\home\Oracle\icici\Oradata\data\tsh1.dbf' SIZE 10M 
extent management local segment space management auto;


To view Temporary Tablespace Usage:-

V$SORT_SEGMENT

v$TEMPSEG_USAGE