5 Replies Latest reply: May 3, 2012 2:20 AM by UweHesse RSS

    undo tablespace

    user10650608
      hi,

      during the last days the undo-tablespace in one of my 11g databases raised to 100%usage. I've increased the size from 512MB to 1024MB but again it's full. So I think that one user (or a programm) is doing some shit in the database, but I don't now how to find the offender. Is it possible to see which user, or which process/transaction needs so much undo?

      thanks for your help

      Edited by: user10650608 on 03.05.2012 07:56
        • 1. Re: undo tablespace
          hitgon
          Hi,

          # UNDO tablespace usage session wise

          SELECT s.username, s.SID, s.serial#, s.logon_time, t.xidusn, t.ubafil,
          t.ubablk, t.used_ublk, t.start_date, t.status
          FROM v$session s, v$transaction t
          WHERE s.saddr = t.ses_addr;


          # UNDO tablespace usage

          select a.process, a.program, a.module, a.machine, b.USED_UREC, c.sql_text
          from v$sql c, v$session a, v$transaction b
          where b.addr = a.taddr
          and a.sql_address = c.address
          and a.sql_hash_value = c.hash_value
          order by b.USED_UREC;

          SELECT s.sid , s.username , t.used_ublk
          FROM v$transaction t
          , v$session s
          WHERE 1 = 1
          AND t.ses_addr = s.saddr

          column username format a15;
          column segment_name format a15;
          SELECT s.sid , s.username , t.used_ublk, round((t.used_ublk*8)/1024) size_in_MB_8kb_Block_size, round((t.used_ublk*16)/1024
          ) size_in_MB_16kb_Block_size
          FROM v$transaction t
          , v$session s
          WHERE 1 = 1
          AND t.ses_addr = s.saddr;

          SELECT distinct rpad(s.sid,3) "SID",S.USERNAME,
          E.SEGMENT_NAME,
          T.START_TIME "Start",
          rpad(T.STATUS,9) "Status",
          round((t.used_ublk*8)/1024) "Size(MB)"
          --T.USED_UBLK||' Blocks and '||T.USED_UREC||' Records' "Rollback Usage"
          FROM DBA_DATA_FILES DF,
          DBA_EXTENTS E,
          V$SESSION S,
          V$TRANSACTION T
          WHERE DF.TABLESPACE_NAME = E.TABLESPACE_NAME AND
          DF.FILE_ID = UBAFIL AND
          S.SADDR = T.SES_ADDR AND
          T.UBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID+E.BLOCKS AND
          E.SEGMENT_TYPE in( 'ROLLBACK','TYPE2 UNDO')


          also refer Re: undo usage by session

          Regards
          Hitgon

          Edited by: hitgon on May 3, 2012 11:34 AM
          • 2. Re: undo tablespace
            415289
            undo_retention is also key factor here as oracle will automatically used the expired data inside undo tablespace based on retention parameter even its 100% full.

            http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams222.htm
            • 3. Re: undo tablespace
              hitgon
              Hi,

              You can think for configure the Alert on undo tablespace

              Oracle Database also provides proactive help in managing tablespace disk space use by alerting you when tablespaces run low on available space. Please refer to "Managing Tablespace Alerts" for information on how to set alert thresholds for the undo tablespace.

              http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo005.htm

              Regards
              Hitgon
              • 4. Re: undo tablespace
                user10650608
                thanks for the help ;-) Now I'm ready to find the offender.
                • 5. Re: undo tablespace
                  UweHesse
                  Let me add that the size of 1 Gig for the Undo Tablespace is by no means a reason for concern. It's actually pretty small.

                  Therefore, I suggest that you consider to focus your maintenance efforts on an area that is more worth your attention.

                  Kind regards
                  Uwe Hesse

                  "Don't believe it, test it!"
                  http://uhesse.com