Undo space related queries

Check the overall status for undos

==================================

select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;

 

TABLESPACE_NAME                STATUS            GB

------------------------------ --------- ----------

UNDOTBS1                       UNEXPIRED 2.29626465

UNDOTBS2                       UNEXPIRED 11.0892944

UNDOTBS1                       EXPIRED   7.20245361

UNDOTBS2                       EXPIRED   1.80932617

UNDOTBS2                       ACTIVE       .015625

Optimal Undo Retention

======================

 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]

------------------------

200

UNDO RETENTION [Sec]

--------------------

10800

OPTIMAL UNDO RETENTION [Sec]

----------------------------

16401 

DB Block Size

==============

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"

FROM v$parameter

WHERE name = 'db_block_size';

DB_BLOCK_SIZE [Byte]

--------------------

4096

Undo Blocks per Second

======================

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

      "UNDO_BLOCK_PER_SEC"

  FROM v$undostat;

UNDO_BLOCK_PER_SEC

------------------

        3.12166667

Calculate Needed UNDO Size for given Database Activity 

======================================================

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]

------------------------

200

UNDO RETENTION [Sec] 

--------------------

10800

NEEDED UNDO SIZE [MByte]

------------------------

131.695313 

The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". 

If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a 

lesser value or increase your UNDO_RETENTION parameter to use the additional space.