Undo tablespace, rollback segments in oracle

Undo Tablespace:

Oracle Database maintains the undo tablespace for maintaining the information of data of transactions and changes on database before committed.

Undo records are used to:

1. Roll back the transaction when a rollback statement issued.

2. Recover the database.

3. Provide read consistency by maintaining the before image of the data.

4. Used for oracle flashback query and flashback features.

Without undo tablespace is oracle database starts?

Yes, Oracle database will start without undo tablespace. At that time undo records will stores in the system tablespace. But it is not preferable, we can notice the warning in the alert log file as system is running without undo tablespace.

We can manage the undo tablespace with the parameters

Undo_tablespace=undotbs

Undo_management=auto

Undo_retention=900

Undo_tablespace parameter:

Once oracle database starts with assigned undo tablespace.

Undo_Management=Auto:

By default, oracle manages the automatic undo management. Server automatically manages the segments and space among the various active sessions.

Undo_retention=900

Undo retention value is by default 900 seconds (15 mins), oracle database retains the data in undo specified or minimum amount of time. Undo data is used for rollback the data and transactions. Committed data is no longer needed, Older information that is older than the current undo retention is said to be expired. Old undo information that is less than the current undo retention period is unexpired.

We can find the undo data/blocks expired / unexpired using below query.

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;