Thursday, August 24, 2006
Segments & Blocks
Table
Table Partition : for partitioned table, each partition a segment & storage parameters can be specified to control them independently.
Cluster : contains one of more tables which share same storage characteristics.
Index : one index segment for each index
Index-organised table
Index partition
Undo segment
Temporary segment
LOB segment
Nested table : The nested table is stored in a separate segment
Bootstrap Segment: helps to initialize data dictionary cache when the database is opened by an instance.
Storage parameters:
|
| 2: | Storage clause specified at segment level except for MINIMUM EXTENT & UNIFORM SIZE tablespace parameters. |
|
| 1: | If not specified at segment level, default storage clause specified at tablespace level used |
|
| 0: | If above two not specified than Oracle Server defaults are used Note : Any alteration of staorage clause applies only to the extents not yet allocated |
Database Blocks:
Consists of one of more OS blocks. DB_BLOCK_SIZE is the default block size. Used for SYSTEM tablespace and most other tablespaces. This default block size can't be changed without recreating the database.
DB_CACHE_SIZE specified the size of the default buffer cache for standard block size. Minimum 1 granule. Default 48MB. (1 granule = 4 MB if SGA_MAX_SIZE<128mb granule =" 16MB).">
For each non-default block sizes, sub-buffer caches must be configured within buffer cache in the init.ora file :
|
| DB_2K_CACHE_SIZE | DB_8K_CACHE_SIZE | DB_32K_CACHE_SIZE |
|
| DB_4K_CACHE_SIZE | DB_16K_CACHE_SIZE |
|
The default values for these are zero, except for the default DB_CACHE_SIZE which must not be configured using its 'n' size variant.
Creating tablespaces with non-standard block size :
|
| CREATE TABLESPACE sktab |
|
Restrictions :
Must configure the non-standard sub-chaches (_4K_ in this case) before the above statement can succeed. These can also be configured while the instance is running.
All TEMPORARY tablespaces including default tablespace must be standard block size.
All partitions of a partitioned object must reside in tablespaces of the same block size.
IOT overflow & out-of-line LOB segments can be stored in a tablespace of different block-size.
Block space Utilization paramenters:
| Parameters |
| Default values |
| : : : : | 1 for data segment; 2 for index segment. Value range : 1-255. Default value is a function of the data block size. Value range : 1-255. 10% 40%. A block is put on free list when its used space falls below PCTUSED. |
| : | A freelist of a segment is a list of blocks that are candidates for future inserts. A segment by default is created with one free list, but it can be configured to be created with a higher number of free lists by setting the FREELIST parameter. |
Segment space management (Automatic):
|
| Tracking in-segment free space using bitmaps instead of FREELISTs. This bitmap is stored in a separate set of blocks referred to as bitmapped blocks (BMBs). It describes the status of each block in the segment with respect to its availability for space. Advantages :
Restrictions :
Configuring : CREATE TABLESPACE sktab Notes :
|
Views : See examples
| DBA_EXTENTS | DBA_SEGMENTS | DBA_TABLESPACES | DBA_DATA_FILES | DBA_FREE_SPAC |
