Undo or Rollback Posted by MOHAMMED ABDUL HAMEED on 8:37 AM

Undo or Rollback is the backbone of the read consistency.Mainly be helpful in Flash Back Technology.

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. 

SQL> show parameter UNDO_RETENTION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

SQL> SELECT SUM(a.bytes)/1024/1024"UNDO_SIZE_IN_MB"
FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

UNDO_SIZE_IN_MB
    ---------------
             70

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;  2  

UNDO_BLOCK_PER_SEC
------------------
        2.52166667

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';  

DB_BLOCK_SIZE [KByte]
---------------------
                 8192

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 ROUND((d.undo_size / (to_number(f.value) *
 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
 FROM (
 SELECT SUM(a.bytes) undo_size
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#
 ) d,
 v$parameter e,
 v$parameter f,
 (
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec
 FROM v$undostat
 ) g
WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size'; 

ACTUAL UNDO SIZE [MByte]       UNDO RETENTION [Sec]      OPTIMAL UNDO RETENTION [Sec]
   ------------------------                             -------------------------             ----------------------------
                  70                                               900                                       3553

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

ACTUAL UNDO SIZE [MByte]     UNDO RETENTION [Sec]      NEEDED UNDO SIZE [MByte]
     ------------------------                     -------------------------                 ------------------------
                      70                                     900                                      17.7304688