Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: Resizing Rollback Segments ....

    Hi ...

    I am using ORACLE 8.1.7.4.1, and i have an problem with my Rollback Segments.
    One of them has become huge (really huge), and i want to return it to its optimal size.
    I know that there is an command SHRINK with the next sintax:
    ALTER ROLLBACK SEGMENT xpto SHRINK TO size

    I would like to know if I can do this without making any harm to the Database (making it OFFLINE first).

    Thankx,

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I like to ensure that there is not active trx in the rollback segment
    prior to shrinking it ...

    sql> SELECT
    r.name, l.Sid, p.spid,
    NVL(p.username, 'no transaction') "Transaction",
    p.terminal "Terminal",
    s.curext,s.curblk
    FROM
    v$lock l,
    v$process p,
    v$rollname r,
    v$rollstat s
    WHERE
    l.Sid = p.pid (+)
    AND TRUNC(l.id1(+) / 65536) = r.usn
    AND l.type(+) = 'TX'
    AND l.lmode(+) = 6
    AND r.usn=s.usn
    ORDER BY r.name;

    If "no transaction" then issue shrink command ...

    HTH
    Gregg

  3. #3
    Join Date
    Dec 2003
    Posts
    76

    Resizing Rollback Segments ...

    In that case, I just need to be sure that there are not any transactions active, right ??

    I just need to run that select you gave me, and if all transactions are inactive, then i can resize the Rollback Segment, right ?


    Thanx,
    Aucrun

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Correct ...

    Gregg

  5. #5
    Join Date
    Dec 2003
    Posts
    76
    Ok, thanx gbrabham !!!

  6. #6
    Join Date
    Dec 2003
    Posts
    76
    One more question ...

    The System Rollback Segment, cannot be resized, right ??
    Or i can shrink it too ???

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    It can be, just ensure you have no activity ...

    Gregg

  8. #8
    Join Date
    Dec 2003
    Posts
    76
    Apparently that worked out, and I've resized the Rollback Segments to its Original Size.

    To better illustrate what happened, and what might happen again, because I work with many thousands of records each time, the following chart demonstrates one of my Rollback Segment (i have 8) before shrink:

    NAME EXTENDS RSSIZE XACTS WAITS GETS OPTSIZE STATUS
    RBS2 0 48754688 1 171 272437 4194304 ONLINE


    And now, it is with its original size ...

    Thanx,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •