Undo and Rollback Segment


Rollback segments are used to store the previous values temporarily when you updates some data in the database. Rollback segments have traditionally stored undo information used by several functions of Oracle. During database recovery, after all changes recorded in the redo log have been applied, Oracle uses rollback segment information to undo any uncommitted transactions. Because rollback segments are stored in the database buffers, this important recovery information is automatically protected by the redo log. Create rollback statement command: 
SQL>create rollback segment rbs tablespace rbsts;
SQL>alter rollback segment rbs online;

You can monitor rollback segments from DBA_ROLLBACK_SEGS
 SQL>select segment_name, status from dba_rollback_segs;


Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:
  • Roll back transactions using ROLLBACK statement
  • Recover the database
  • Provide read consistency
  • Analyze data as of an earlier point in time by using Flashback Query

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

SQL>show parameter undo;

undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTEST 

SQL>create undo tablespace undotest datafile'/opt/test/undotest.dbf' size 100m 
autoextend on next 10m maxsize unlimited retention noguarantee;

System parameters

SQL>alter system set undo_retention=900 scope=both;
SQL>alter system set undo_tablespace= undotest  scope=both;
SQl>alter system set undo_management= auto scope=spfile;
SQL>shutdown immediate

UNDO_MANAGEMENT is a static parameter, so database needs to be restarted.

Related Post:- Recover Undo Tablespace

People who read this post also read :


Post a Comment


Twitter Delicious Facebook Digg Stumbleupon Favorites More