Explain different types of segment. Data segment, Index , Rollback segments

[dtech-team]

There are four types of segments used in Oracle databases:

- data segments
- index segments
- rollback segments
- temporary segments

[dtech-team]

Data Segments:

There is a single data segment to hold all the data of every non clustered table in an oracle database. This data segment is created when you create an object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command. Also, a data segment is created for a cluster when a CREATE CLUSTER command is issued.

The storage parameters control the way that its data segment's extents are allocated. These affect the efficiency of data retrieval and storage for the data segment associated with the object.

Index Segments:

Every index in an Oracle database has a single index segment to hold all of its data. Oracle creates the index segment for the index when you issue the CREATE INDEX command. Setting the storage parameters directly affects the efficiency of data retrieval and storage.

Rollback Segments

Rollbacks are required when the transactions that affect the database need to be undone. Rollbacks are also needed during the time of system failures. The way the roll-backed data is saved in rollback segment, the data can also be redone which is held in redo segment.

A rollback segment is a portion of the database that records the actions of transactions if the transaction should be rolled back. Each database contains one or more rollback segments. Rollback segments are used to provide read consistency, to rollback transactions, and to recover the database.

Types of rollbacks:

- statement level rollback
- rollback to a savepoint
- rollback of a transaction due to user request
- rollback of a transaction due to abnormal process termination
- rollback of all outstanding transactions when an instance terminates abnormally
- rollback of incomplete transactions during recovery.

Temporary Segments:

The SELECT statements need a temporary storage. When queries are fired, oracle needs area to do sorting and other operation due to which temporary storages are useful.

The commands that may use temporary storage when used with SELECT are:
GROUP BY, UNION, DISTINCT, etc.


Thanks to : http://www.careerride.com/Oracle-Architecture.aspx#Loader