STORAGE MANAGEMENT

logical storage component of Oracle Database i.e. Data Blocks, Extents and Segment

A.     Data Blocks is smallest logical unit to store Oracle Data.
ii) One data block represent specific number of bytes on physical hard disk.
iii) Data Block in Oracle is also called as logical block
iv) Data Block size is usually multiple of operating system block size
v) You can have multiple block sizes with in single database (max. five)
vi) Block Size is specified by initialization parameter DB_BLOCK_SIZE

Data Block Format

a)  Header : contains generic information like block address and type of segment (index,   data..)
b)  Table Directory :contains information about table having rows in that block
c)  Row Directory : contains information about actual row contained in that block
d)  Free Space : available space in data block for additional row or update of row which require    more space.
e) Row Data : contains table or index data.First three component of data block (Header, Table &    Row directory) collectively known as Overhead.

B.      Extentis collection of contiguous data blocks.
ii) One or more extents make up a segment

C.      Segment is set of extents allocated for specific data structure (like table or index).
ii) Various kind of segments are table, index, cluster, rollback, temporary …
iii) Important views for segments are dba_segments, user_segments, all_segments
iv) In a Segment, first block of first extent contains segment header information

CREATE TABLESPACE command creates a dictionary managed tablespace

Here the DEFAULT STORAGE parameter is used to specify the size of extents allocated to segments created within the tablespace.

CREATE TABLESPACE data

DATAFILE '/u01/student/dbockstd/oradata/USER350data01.dbf' SIZE 20M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (

INITIAL 128K

NEXT 40K

PCTINCREASE 50

MINEXTENTS 1

MAXEXTENTS 999);

INITIAL specifies the initial extent size (the first extent allocated).

NEXT specifies the size of the next extent (2^nd, 3^rd, etc).

PCTINCREASE refers to the percentage by which each next extent (beginning with the third extend) will grow. The size of each subsequent extent is equal to the size of the previous extent plus this percentage increase.

MINEXTENTS and MAXEXTENTS parameters specify the minimum and maximum number of extents allocated by default to segments that are part of the tablespace.

Locally vs. Dictionary Managed Tablespaces

Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

          SQL>CREATE TABLESPACE TS1 DATAFILE ‘/oradata/ts1_01.dbf’ size 50m

                    EXTENT MANAGEMENT DICTIONARY

                    DEFAULT STORAGE(INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

SQL> ALTER TABLESPACE TS1

DEFAULT STORAGE (

INITIAL 2M

NEXT 2M

MAXEXTENTS 999 );

                  

Locally Managed Tablespaces (LMT):

SQL>CREATE TABLESPACE TS2 DATAFILE ‘/oradata/ts2_01.dbf’ size 50m

         EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL>CREATE TABLESPACE TS3 DATAFILE ‘/oradata/ts3_01.dbf’ size 50m

          EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Change normal tablespaces to locally managed :

SQL>SELECT tablespace_name,extent_management FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN

------------------------------ ----------

SYSTEM                         DICTIONARY

TEMP                           LOCAL

USERS                          DICTIONARY

TAB                            DICTIONARY

IDX                            DICTIONARY

SYSAUX                         LOCAL

UNDO                           LOCAL

SQL>execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');
PL/SQL procedure successfully completed.

Change normal tablespaces to dictionary managed :

SQL>SELECT tablespace_name,extent_management FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN

------------------------------ ----------

SYSTEM                         DICTIONARY

TEMP                           LOCAL

USERS                          LOCAL

TAB                            DICTIONARY

IDX                            DICTIONARY

SYSAUX                         LOCAL

UNDO                           LOCAL

SQL>execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL ('USERS');
PL/SQL procedure successfully completed.

Using the Data Dictionary to Manage Storage

Periodically you will need to obtain information from the data dictionary about storage parameter settings. The following views are useful.

·         DBA_EXTENTS – information on space allocation for segments.

·         DBA_SEGMENTS – stores information on segments.

·         DBA_TABLESPACES – a row is added when a tablespace is created.

·         DBA_DATA_FILES – a row is added for each datafile in the database.

·         DBA_FREE_SPACE – shows the space in each datafile that is free.