1.Type of segments
Segments are space-occupying objects in a database.

Table
The most common means of storing data within a database.
Data within a table segment is stored in on particular order,and the DBA has little control
over the location of rows within the blocks in a table.

Table Partition
Scalability and availability are major concerns when there is a table in a database with high concurrent usage.
Each partition can reside in a different tablespace.And each partition is a segment,and storage parameters can
be specified to control the independently.

Cluster
Rows in a cluster are stored based on key column values.A cluster may contain one or more tables.Tables in a cluster
belong to the same segment and share the same storage characteristics.The rows in a clustered table can be accessed with
an index or hasing algorithm.

Index
All the entries for a particular index are stored within one index segment.The purpose of this segment is to look up the
location of rows in a table based on specified key.

Index-Organized Table
In an index-organized table,data is stored within the index based on the key value.An index-organized table dose not need a table
lookup,becauase all the data can be retrived directly from the index tree.

Index partition
An index can be partitioned and spread accorss serval tablespaces.In this case,each partition in the index corresponds to a segment
and cannot span mutiple tablespace.The primary use of a partioned index is to minimize contention by spreading index I/O.

Undo Setment
An undo segment is used by a transaction that is making chages to a database.Before changing the data or index blocks,the old values
is stored in the undo segment.This allow a user to undo change made.

Temporary Segment
When a user executes commands such as CREATE INDEX,SELECT DISTINCT,and SELECT GROUP BY,the Oracle server tries to perform sorts in memory.
When a sort needs more space than the space available in memory,intermediate results are written to the disk.Temporary segments are used
to store these intermediate results.

LOB Segment
LOG segment is used to stored large objects,such as text documents,images,or videos.LOB type of column is a separate segment,known as
LOB segment,The table contains only a locator or a pointer to the location of the corresponding LOB data.

Nested Table
A column in a table may be made up of a user-definned table as in the case of items within an order.In such cases,the inner table,which
is known as a nested table,is stored as a sparate segment.

Bootstrap Segment

A bootstrap segment, also known as a cache segment,is created by the sql.bsp script when a database is created.This segment helps to initialize
the data dictionary cache when the database is opened by an instance.
The bootstrap segment cannot be queried or updated and dose not require any maintenance by the DBA.

2.Storage Parameters
A storage caluse can be specifed at segment level to control how extents are allocated to a segment
.Any storage marapeter specified at the segment level overrides the corresponding option set at the tablespace level,except for the
MINIMUM EXTENT or UNIFORM SIZE tablespace parameter
.When storage parameters are not set explicitly at the tablespace level,they default to those at tablespace level
.When storage parameters are not set explicitly at the tablespace level,the Oracle server system defaults are used

Other Considerations
.If storage parameters are alterd,the new options apply only to the extents not yet allocated
.Some parameters cannot be specified at the tablespace level.These parameters need to be specified at the segment level only
.If minimum extent size has been specified for the tablespace ,this size applies to all extents that are allocated for segments in
the talbespace in the future.

3.Extent Allocation and Deallocation
.Allocated when segment is :
–Created
–Extented
–Altered

.Deallocated when the segment is :
–Dropped
–Altered
–Truncated

4.Block Space Utilization Parameters
Block space utilization parameters can be used to control the use of space in data and index segments.

INITRANS and MAXTRANS specify the initial and the maximum number of transaction slots that are created in an index 
or a data block.The transaction slots are used to store information about transactions that are making chages to the
block at a point in time.A transaction uses only one transaction slot,even if it is changing more than one row or index entry.
 
INITRANS,which defaults to 1 for a data segment and 2 for an index segment,guarantees a minimum level of concurrency.
 
MAXTRANS,which has a default value of 255,sets the limit for a number of concurrent transactions tha can make changes
to a data or an index block.When set ,this value restricts use of space for transaction slots and therefore guarantees
that there is sufficient space in the block for use by row or index data.

PCTFREE

PCTUSED

FREELISTS

5.Data Block Management

Two methods are available for managing data blocks
.Automatic Segment-Space Management
–It is method of managing free space inside database segments
–Tracking in-segment free and used space is done using bitmaps as opposed to free lists
–This method provides:
- Ease of management
-PCTUSED,FREELISTS,FREELIST GROUPS are managed automatically.
- Better space utilization
-All objects and especially objects with grately varyring row sizes utilize space more efficiently
- Better performance for concurrent INSERT operations
-Run-time adjustments to variations in concurrent access are improved
–Restriction:Can not be used for tablespaces which will contain LOBs.

–Automatic segment-space management can be enable at the tablespace level only,for locally managed tablespace

1
2
3
4

CREATE TABLESPACE data_01
DATAFILE '/u01/oradata/data_01.dbf' size 1921M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO;

–After a tablespace is created , the specifications apply to all segments created in the tablespace.

.Manual Configuration
Parameter such as PCTFREE,PCTUSED,and FREELIST are used in manual management.

5.Data Dictionary Views
DBA_EXTENTS
DBA_SEGMENTS
DBA_FREE_SPACE
DBA_DATA_FILES
DBA_TABLESPACES

When a tablespace with one or more files is created, a row is added to DBA_TABLESPACES.
For each file in the database, a row is added to DBA_DATA_FILES.At this stage,the space in eache data file,
excluding the file header,shows up as one free extent in DBA_FREE_SPACE.
When a segment is created,a row is visible in DBA_SEGMENTS.The space allocated to the extents in this segment
can be viewed from DBA_EXTENTS,while DBA_FREE_SPACE is adjusted to show lower free space in the files where
the extents have been created for the segment.
All the space in a file(excluding the header block)mush be accounted for either in DBA_FREE_SPACE or in DBA_EXTENTS.

Popularity: 27% [?]