25-01-2006

Non system table space is divided into following categories:

1. Table
2. Index
3. Tools

Roll back table space

If we want to make modification in the data, then data to be changed is brought from hard disk to RAM. Now you can change the data. Before u start changing data the original data is stored in roll back segment. As a result you can any time recover the original data. Original data can be recovered also by not saving the data.

Before Oracle 9i user was allowed to create roll back segment. From Oracle 9i onwards Oracle started the function Auto roll back and user interference in roll back segment was stopped. However there is a scope of user governing roll back segment if setup is “manual”

One of the parameter is undo management=auto

If this is set to auto, as show above then you have to create rollback segment space at the time of creation of database.

System table space, Non-system table space and Roll back system table space fall under permanent table space.

Actually Temporary table space is permanent. Then why is called as temporary? It is so called because of the objectives it contains are temporary. For E.g.: Temporary segment.

*: Temporary table space doesn’t allow you to create any permanent object in temporary space or in other words DBAS are not allowed to create temporary segments.

*: Temporary segments can be created in permanent table space (System, Non system and Roll back)

The bottom line is that temporary operations like sorting takes place in Temporary segment.

Table spaces are classified also according to extent management:

1. Dictionary management
2. Locally management

Logically Structure:

TableàSegments (table, index, cluster, rollback, temporary, partition, mview, snapshots) àExtentsàData blocks (contiguous)

*: Divide large group or table to fragments there by sorting is made easy.

Table space is made up of data files. Extents are written to data file. Extents belong to table, index, cluster, rollback etc. If table space gets full, you can create data files to increase your space.