This discussion is archived
10 Replies Latest reply: Oct 9, 2012 1:30 AM by Hemant K Chitale RSS

how to find the maximum undo space used by the database

user13364377 Newbie
Currently Being Moderated
Hi All,

How can we find out the maximum undo space used by a database till now?

DB version:10.2.0.5
OS: AIX
  • 1. Re: how to find the maximum undo space used by the database
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check
    http://blog.mydream.com.hk/howto/how-to-determine-undo-usage-in-oracle
  • 2. Re: how to find the maximum undo space used by the database
    user13364377 Newbie
    Currently Being Moderated
    Thanks for the link,

    I have gone through the link... But is there a way where we can determine the maximum space used by the undo tablespace ?
  • 3. Re: how to find the maximum undo space used by the database
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Why you need to do this ? usually maximum will be the same size of datafile
  • 4. Re: how to find the maximum undo space used by the database
    user13364377 Newbie
    Currently Being Moderated
    Wanted to check the maximum undo space consumed for one of our non-prod databases as we have similar type of change scheduled for our production database
  • 5. Re: how to find the maximum undo space used by the database
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If the undo tablespace datafile has autoextend on and has grown larger than the initially created size, then the current size is the peak (I'd rather use the word "peak" not "maximum") undo usage.

    Hemant K Chitale
  • 6. Re: how to find the maximum undo space used by the database
    user13364377 Newbie
    Currently Being Moderated
    Hemant,

    Even if the undo tablespace has reached its peak, the space can shrink based upon the undo retention set, right?
  • 7. Re: how to find the maximum undo space used by the database
    user13364377 Newbie
    Currently Being Moderated
    Suppose if the undo tablespace used space was 100G 1 week back and now the used space is 50G. So how can we find out that it consumed 100G last week?
  • 8. Re: how to find the maximum undo space used by the database
    abdul: Newbie
    Currently Being Moderated
    hell0,
    run this as sysdba
    SET TERMOUT OFF;
    COLUMN current_instance NEW_VALUE current_instance NOPRINT;
    SELECT rpad(instance_name, 17) current_instance FROM v$instance;
    SET TERMOUT ON;
    
    PROMPT 
    PROMPT 
    SET ECHO        OFF
    SET FEEDBACK    6
    SET HEADING     ON
    SET LINESIZE    180
    SET PAGESIZE    50000
    SET TERMOUT     ON
    SET TIMING      OFF
    SET TRIMOUT     ON
    SET TRIMSPOOL   ON
    SET VERIFY      OFF
    
    CLEAR COLUMNS
    CLEAR BREAKS
    CLEAR COMPUTES
    
    COLUMN instance_name  FORMAT a9               HEADING 'Instance'
    COLUMN undo_name      FORMAT a30              HEADING 'Undo Name'
    COLUMN tablespace     FORMAT a11              HEADING 'Tablspace'
    COLUMN in_extents     FORMAT a23              HEADING 'Init / Next Extents'
    COLUMN m_extents      FORMAT a23              HEADING 'Min / Max Extents'
    COLUMN status         FORMAT a8               HEADING 'Status'
    COLUMN wraps          FORMAT 99,999           HEADING 'Wraps' 
    COLUMN shrinks        FORMAT 99,999           HEADING 'Shrinks'
    COLUMN opt            FORMAT 999,999,999,999  HEADING 'Opt. Size'
    COLUMN bytes          FORMAT 999,999,999,999  HEADING 'Bytes'
    COLUMN extents        FORMAT 999              HEADING 'Extents'
    
    BREAK ON instance_name SKIP 2
    
    COMPUTE SUM LABEL 'Total: ' OF bytes ON instance_name
    
    SELECT
        i.instance_name                           instance_name
      , a.owner || '.' || a.segment_name          undo_name
      , a.tablespace_name                         tablespace
      , TRIM(TO_CHAR(a.initial_extent, '999,999,999,999')) || ' / ' ||
        TRIM(TO_CHAR(a.next_extent, '999,999,999,999'))                    in_extents
      , TRIM(TO_CHAR(a.min_extents, '999,999,999,999'))    || ' / ' ||
        TRIM(TO_CHAR(a.max_extents, '999,999,999,999'))                    m_extents
      , a.status                                  status
      , b.bytes                                   bytes
      , b.extents                                 extents
      , d.shrinks                                 shrinks
      , d.wraps                                   wraps
      , d.optsize                                 opt
    FROM
                    gv$instance       i
        INNER JOIN  gv$rollstat       d   ON (i.inst_id      = d.inst_id)
        INNER JOIN  sys.undo$         c   ON (d.usn          = c.us#)
        INNER JOIN  dba_rollback_segs a   ON (a.segment_name = c.name)
        INNER JOIN  dba_segments      b   ON (a.segment_name = b.segment_name)
    ORDER BY
        i.instance_name
      , a.segment_name;
    hopes this help.
  • 9. Re: how to find the maximum undo space used by the database
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    http://allappsdba.blogspot.com/2012/04/queries-related-to-undo-tablespace.html
  • 10. Re: how to find the maximum undo space used by the database
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    uuh.. It wouldn't have grown to 100GB if it wasn't using 100GB ? Unless you think that there are bugs causing the undo datafile to grow even when segments are not growing.


    Hemant K Chitale

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points