Undo Queries

 

SQL>
SQL> select sum(bytes /(1024*1024*1024)) from dba_undo_extents where status='EXPIRED';
select sum(bytes /(1024*1024*1024)) from dba_undo_extents where status='ACTIVE';
select sum(bytes /(1024*1024*1024)) from dba_undo_extents where status='UNEXPIRED';
SUM(BYTES/(1024*1024))
----------------------
               10.6875

SQL>
SUM(BYTES/(1024*1024))
----------------------
             40788.875

SQL>

SUM(BYTES/(1024*1024))
----------------------
               154.375

SQL> show parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS02
SQL>


SQL> select file_id, autoextensible
  from dba_data_files
    where tablespace_name = 'UNDOTBS02'
    /  2    3    4

   FILE_ID AUT
---------- ---
         3 NO
        57 NO
        43 NO


select count(*), status
 from dba_undo_extents
 group by status
 /

SQL> select count(*), status
 from dba_undo_extents
 group by status
 /  2    3    4

  COUNT(*) STATUS
---------- ---------
       171 EXPIRED
      3893 UNEXPIRED
        13 ACTIVE



select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024/1024),2) GB from dba_undo_extents group by status order by status


STATUS    NUM_EXTENTS NUM_BLOCKS         GB
--------- ----------- ---------- ----------
ACTIVE              9      13312         .1
EXPIRED           171       1368        .01
UNEXPIRED        3982    5873960      44.81


SQL> select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024/1024),2) GB from dba_undo_extents group by status order by status;

STATUS    NUM_EXTENTS NUM_BLOCKS         GB
--------- ----------- ---------- ----------
ACTIVE              2       9216        .07
EXPIRED           147       1176        .01
UNEXPIRED        4489    5865352      44.75



SQL> select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024/1024),2) GB from dba_undo_extents group by status order by status;

STATUS    NUM_EXTENTS NUM_BLOCKS         GB
--------- ----------- ---------- ----------
ACTIVE              2       9216        .07
EXPIRED           147       1176        .01
UNEXPIRED        4489    5865352      44.75

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

TABLESPACE_NAME                STATUS            GB
------------------------------ --------- ----------
UNDOTBS02                      ACTIVE          .125
UNDOTBS02                      UNEXPIRED 44.7410278
UNDOTBS02                      EXPIRED   .008850098

SQL>
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
  2    3         ROUND((d.undo_size / (to_number(f.value) *
  4         g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  5    FROM (
  6         SELECT SUM(a.bytes) undo_size
  7            FROM v$datafile a,
  8                 v$tablespace b,
  9                 dba_tablespaces c
 10           WHERE c.contents = 'UNDO'
 11             AND c.status = 'ONLINE'
 12             AND b.name = c.tablespace_name
 13             AND a.ts# = b.ts#
 14         ) d,
 15         v$parameter e,
 16         v$parameter f,
 17         (
 18         SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 19                undo_block_per_sec
 20           FROM v$undostat
 21         ) g
 22  WHERE e.name = 'undo_retention'
 23    AND f.name = 'db_block_size'
 24  /

ACTUAL UNDO SIZE [MByte]
------------------------
UNDO RETENTION [Sec]
--------------------------------------------------------------------------------
OPTIMAL UNDO RETENTION [Sec]
----------------------------
                   46080
10800
                        3084


SQL> col ACTUAL UNDO SIZE [MByte] for a10
SP2-0158: unknown COLUMN option "UNDO"
SQL>
SQL> set lines 200
SQL> /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]                                                                                 OPTIMAL UNDO RETENTION [Sec]
------------------------ ---------------------------------------------------------------------------------------------------- ----------------------------
                   46080 10800                                                                                                                        3084

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

UNDO_BLOCK_PER_SEC
------------------
           1912.46

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
  2         SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
  3    4         g.undo_block_per_sec) / (1024*1024)
  5        "NEEDED UNDO SIZE [MByte]"
  6    FROM (
  7         SELECT SUM(a.bytes) undo_size
  8           FROM v$datafile a,
  9                v$tablespace b,
 10                dba_tablespaces c
 11          WHERE c.contents = 'UNDO'
 12            AND c.status = 'ONLINE'
 13            AND b.name = c.tablespace_name
 14            AND a.ts# = b.ts#
 15         ) d,
 16        v$parameter e,
       v$parameter f,
 17   18         (
 19         SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 20           undo_block_per_sec
 21           FROM v$undostat
 22         ) g
 23   WHERE e.name = 'undo_retention'
 24    AND f.name = 'db_block_size'
 25  /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]                                                                                 NEEDED UNDO SIZE [MByte]
------------------------ ---------------------------------------------------------------------------------------------------- ------------------------
                   46080 10800                                                                                                              161363.813