10 Replies Latest reply: Oct 9, 2012 3:30 AM by Hemant K Chitale RSS

    how to find the maximum undo space used by the database

    user13364377
      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
          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
            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
              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
                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
                  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
                    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
                      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:
                        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
                          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
                            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