Undo segment相关信息与实验

Implement Automatic Undo Management(rather than Manual)
    Undo = rollback
    Undo segment ->save old value(undo data)&relevant location
    Undo segment header ->contains a transaction table->stores the information about the current transactions using the undo segement

    A serial transaction(concurrent transactions included) uses only one undo segment to store all of its undo data.
    purpose ->transaction rollback,transaction recovery, read consistency
        transaction rollback: restores the original values back to the modifiled row.
        transaction recovery: instance fails while transactions are in  progress--> undo any uncommitted changes(when DB is opened again) <-- changes made to the undo segment are also protected by the online redo log files.
        read consistency:a.transactions in progress, other users should not see any uncommitted changes made by these transactions.
                                      b. a statement should not see any changes committed in execution

 

 

Read Consistency
       Order: executing a SELECT statement
                  determines current SCN& ensures any changes not committed before the SCN are not processed by the statement.
                  whether a data block is old or not depends one the SCN(compare)
The Oracle server automatically manages the creation, allocation, and  tuning of undo segments.
   

 

Types of Undo Segments
SYSTEM Undo Segment -> created in the SYSTEM tablespace when a DB is created.
                                        -> used only for changes made to objects in the SYSTEMTBS

 

Non-SYSTEM Undo Segments -> A DB that has multiple tablespaces needs at least one non-SYSTEM  segment for manual mode or one UNDO tablespace for auto mode.
                                     Deferred: Used when tablespaces are taken offline immediate, temporary, or for recovery

Automatic Undo Management: Concepts
* Undo data is managed using an UNDO tablespace.
* You allocate one UNDO tablespace per instance with enough space for the workload of the instance.
*Undo segments are created with the naming convention: _SYSSMUn$

 

Automatic Undo Management: Configuration
* Configure two parameters in the initialization file(must&only):
– UNDO_MANAGEMENT：AUTO/MANUAL(not recommended), can not be changed after DB starts.
– UNDO_TABLESPACE：specifies a particular UNDO TBS to be used.
                                      can be dynamically altered.
                    --SQL> ALTER SYSTEM SET undo_tablespace = UNDOTBS;
                    create at least one UNDO tablespace(auto,one active).
                    >= 1 UNDO tablespace may exist, only one can be active.
                    spfilewade.ora   ---refer to manually create a DB
    

Automatic Undo Management: UNDO Tablespace creation
1.created with a DB by adding a clause in the CREATE DATABASE command
CREATE DATABASE db01
. . .
UNDO TABLESPACE undo1               
DATAFILE '/u0