Chapter 11 Managing Undo Data

Explain the Purpose of Undo (used for Atomicity, Consistency, Isolation)
Undo data is the inforamtion needed to reverse the effects of DML statements.
To roll back a transation menas to use data from the undo segments to construct an image of the data as it was before the transaction occurred.

Understand How Transactions Generate Undo
When a transaction starts, Oracle will assign it to one (and only one) undo segment.
Any one transaction can only be protected by one undo segment.
Oracle will auotmatically add another extent to the segment if the undo segment is full.
Oracle will spawn new undo segments on demand to ensure no two transactions share undo segments.
Undo data must be retained until transaction commits.
Undo data written to undo segment in the database buffer cache. If necessary, the DBWn will write the changed blocks of undo data to the undo segment in the datafiless.
Oracle does not gurantee Consistency.

Undo Data Category:
Active undo is undo data that might be needed to roll back transactions in progress. (Cannot be overwritten)
Expired undo is undo data from committed transactions. (Can be overwritten)
Unexpired undo is from committed transaction but needed for consistent reads. (Attempt not to overwrite only if there is a shortage of undo space)

oldest undo data within undo segment will be overwritten.

Manage Undo
Error Conditions related to Undo
Principle:
1. always enough undo space for transactions to continue
2. always enough undo data for queries to succeed

ORA-30036 "unable to extend segment in undo tablespace"
Undo tablespace is absolutely full of active undo data

ORA-1555 "snapshot too old"
Undo data has been overwritten

Default is to let transaction succeed to overwrite unexpired undo data so as to prevent ORA-30036 error. Only the statement hit the error is rolled back.

UNDO_MANAGEMENT=AUTO (static)
UNDO_TABLESPACE to be specified an undo tablespace if above is used.
UNDO_RETENTION is a target set in seconds (optional), time to keep the inactive/unexpired undo data and determine when the undo data become expired

UNDO RETENTION GUARANTEE (tablespace level) meaning that undo data will never be overwritten until the time specified by the UNDO_RETENTION has passed.

Creating and Managing Undo Tablespaces (Space Management option cannot be specify)
CREATE UNDO TABLESPACE tbs_name
DATAFILE datafile_name SIZE size [RETENTION NOGUARANTEE GUARANTEE];

ALTER TABLESPACE tbs_name RETENTION [GUARANTEE NOGUARANTEE];

Only one active undo tablespace will be in use at a time, online.