This discussion is archived
5 Replies Latest reply: May 3, 2012 12:20 AM by UweHesse RSS

undo tablespace

user10650608 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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
    KuljeetPalSingh Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks for the help ;-) Now I'm ready to find the offender.
  • 5. Re: undo tablespace
    UweHesse Expert
    Currently Being Moderated
    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

Legend

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