STORAGE MANAGEMENT
In oracle, database space demanding objects are called segments. There are 5 different segments i.e. tables, indexes, temporary, undo, rollback segments.
- Within a single server, we can create multiple database.
- Database contains one or more tablespace.
- One tablespace can have one or more segments.
- Each segment is made up of one or more extents.
- One extent is made up of one or more contagious oracle blocks (COB)
WHAT IS DYNAMIC SPACE ALLOCATION (DSA):
Consider a user data which consists of employee, department, and salary as segments
Here e1, d1, s1, e2 are called extents. If e1 is filled up, oracle automatically create another extent e2, this is called DYNAMIC SPACE ALLOCATION (DSA). DSA is not recommended at all times because performance decreases as extents increase. This is the main disadvantage of DSA.
We can minimize the DSA by specifying the particular size for extents which is possible by using the “STORAGE PARAMETERS”.
But we cannot avoid the DSA totally, we can minimize count of extents present in a segment by performing the “RE-ORGANISATION “.
WHAT IS RE-ORGANISATION? .
To overcome the disadvantage of DSA, re-organisation concept is introduced, collecting all extents into one datafile and recovering the data into one segment for better performance.
When a database object/segment is created, it has always a set of storage parameters.
STORAGE PARAMETERS:
Storage parameters say how efficiently the space in the database is used.
Initial:
It specifies the size of the first extent of the segment.
Next:
It specifies the size of the second extent of the segment. The default value for NEXT is always the size of 5 data blocks. For each new extent, the NEXT is set to the previous size of NEXT multiplied by (1 + PCTINCREASE/100).
Min extents:
It specifies the minimum number of extents that must be allocated to the segment when it is created.
The default and minimum value is 1, meaning that Oracle allocates only the initial extent, except for rollback segments.
Max:
It specifies the maximum number of extents that must be allocated to the segment.
Pct increase:
It specifies the percent by which the third and subsequent extents grow over the preceding extent. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. It can be changed.
Pct Free:
It is a block storage parameter which specifies the amount of free space Oracle should leave in a database block for future updates. Oracle will continue adding new rows to the block until the remaining space is equal to the PCTFREE parameter. The default value for the PCTFREE parameter is PCTFREE=10. The PCTFREE parameter is only used when an index is created. After index creation, new index block entries are made and PCTFREE is ignored.
Pct used:
It is a storage parameter in Oracle which specifies when a database block is empty enough for Oracle to add it to the free list. When the percentage of used space in a block is greater than the PCTUSED parameter, Oracle will not add new rows to the block. The default settings for all Oracle tables are PCTUSED=40.
Dictionary Managed Tablespaces (DMTS):
Oracle use the data dictionary (tables in the SYS ) to track free and allocated extents for tablespaces that is called Dictionary Managed mode.
Free space is recorded in the SYS.FET$ table and used space in the SYS.UET$ table.
Dictionary Managed is default mode for tablespaces up to version 8i.
Storage Parameters (initial, next, minextents, maxextents and pctincrease) can be specified for Dictionary Managed tablespaces only.
Storage Parameters can be specified by DBA while creating the tablespace, or can be specified by User while creating the table.
If DBA or User not specified any Storage Parameters oracle uses the oracle Storage Parameters.
HOW TO CHECK STORAGE PARAMETER SIZE:
Select tablespace_name,initial_extent,next_extent,min_extents,max_extents,pct_increase,extent_management,segment_space_management from dba_tablespaces;
DICTIONARY MANAGED TABLESPACE:
Create tablespace <tablespace name> datafile <’datafile location’> size extent management dictionary default storage (initial <size> next <size> minextents <count> maxextents <count> pctincrease <percentage>);
HOW TO SET STORAGE PARAMETERS WHILE CREATING A TABLE:
Create table <table_name> (specify all columns) storage (initial <size> next <size> minextents <count> maxextents <count> pct increase <percentage>);
Locally Managed Tablespaces (LMTS):
Oracle use the same tablespace one of the datafile, a tablespace manages it’s own free and used space within a bitmap structure.
Locally Managed is default mode for tablespaces up to version 9i.
Locally managed tablespaces cannot have the storage parameters. It maintains it extents in the form of “AUTOALLOCATE “ and “UNIFORM” .
DIFFERENCES BETWEEN AUTOALLOCATE AND UNIFORM SIZE:
AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB, and eventually to 64MB. This is the recommended option for a low or unmanaged environment.
UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. The default size is 1M. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object, such as a table or an index is created.
HOW TO CHECK STORAGE PARAMETER SIZE:
Select tablespace_name,initial_extent,next_extent,min_extents,max_extents,pct_increase,extent_management,segment_space_management from dba_tablespaces;
LOCALLY MANAGED TABLESPACE:
Create tablespace <tablespace name> datafile <’datafile location’> size <size> extent management local autoallocate;
Create tablespace <tablespace name> datafile <’datafile location’> size <size> extent management local uniform size <size>;
SEGMENT SPACE MANAGEMENT:
Oracle maintains the segment space management auto and manual.
If segment space management manual, it allows to convert the DMTS è LMTS and vice versa.
SEGMENT SPACE MANAGEMENT IN LOCALLY MANAGED TABLESPACE:
Create tablespace <tablespace name> datafile <datafile location> size <size> extent management local segment space management manual;
CONVERSION BETWEEN LOCALLY MANAGED TABLESPACE AND DICTIONARY MANAGED TABLESPACE:
Exec dbms_space_admin.tablespace_migrate_to_local (‘tablespace name’);
Exec dbms_space_admin.tablespace_migrate_from_local (‘tablespace name’);
Loading...
