MANAGE UNDOTABS

1.manual 

 undo_management=manual

 transactions                    
 transactions_per_rollback_segment

 rollback_segments =('rbs1','rbs2')

 

 create rollback segment rbs1 tablespace undotbs1;            privs seg

 create public rollback segment prbs1 tablespace undotbs1   public seg

 

 alter rollback segment rbs1 online;

 

 note: create rollback segment default offline,we need to online;     

           when instance startup, the oracle open private seg(rollback_segments ),then caculate the numbers of rollbacksegments with transactions/transactions_per_rollback_segment.if rollback_segments = the result, oracle open database,other ,get public segs then open database.

 

2.auto

   undo_management=auto

   undo_tablespace=undotbs1

   if undotablespace is to small,autoetend off ,when we execute big transactions, we encounter some errors.ORA-30036: unable to extend segment by 8 in undo tablespace 'SMALLUNDO'.  ORA-06512: at line 3

  

3.  it is a common misconception that undo is used to restore the database physically to the
way it was before the statement or transaction executed, but this is not so. The database is
logically restored to the way it was—any changes are logically undone—but the data struc-
tures, the database blocks themselves, may well be different after a rollback. The reason for
this lies in the fact that, in any multiuser system, there will be tens or hundreds or thousands
of concurrent transactions. One of the primary functions of a database is to mediate concur-
rent access to its data. The blocks that our transaction modifies are, in general, being modified
by many other transactions as well. Therefore, we cannot just put a block back exactly the way
it was at the start of our transaction—that could undo someone else’s work!
For example, suppose our transaction executed an INSERT statement that caused the allo-
cation of a new extent (i.e., it caused the table to grow). Our INSERT would cause us to get a
new block, format it for use, and put some data on it. At that point, some other transaction
might come along and insert data into this block. If we were to roll back our transaction, obvi-
ously we cannot unformat and unallocate this block. Therefore, when Oracle rolls back, it is
really doing the logical equivalent of the opposite of what we did in the first place. For every
INSERT, Oracle will do a DELETE. For every DELETE, Oracle will do an INSERT. For every UPDATE,
Oracle will do an “anti-UPDATE,” or an UPDATE that puts the row back the way it was prior to our
modification.

 

4.Generates the Most and Least Undo

   an INSERT will, in general, generate the least amount of undo, since all Oracle needs to record for this is a rowid to “delete.

   The INSERT generated very little undo that needed to be logged.

   The UPDATE generated an amount equal to the before image of the data that was changed.

   The DELETE generated the entire set of data written into the undo segment.

   an update of an unindexed column not only executes much faster, but will tend to generate significantly less undo than an update of an indexed column.

 

5.ORA-01555: snapshot too old Error,provide for read consistency,
that results in the ORA-01555 error

   CAUSE:

   • The undo segments are too small for the work you perform on your  system.
   • Your programs fetch across COMMITs (actually a variation on the preceding point).
We covered this in the last chapter.
   • Block cleanout.  Delayed Block Cleanout,To clean out the
block, Oracle determines the undo segment used for the previous transaction (from the blocks
header) and then determines if the undo header indicates whether it has been committed or
not. This confirmation is accomplished in one of two ways. One way is that Oracle can deter-
mine that the transaction committed a long time ago, even though its transaction slot has
been overwritten in the undo segment transaction table. The other way is that the COMMIT SCN
is still in the transaction table of the undo segment, meaning the transaction committed a short time ago, and its transaction slot hasn’t been overwritten.if its transaction slot has been overwritten,when clear block ,result in ORA-01555.