I am using ORACLE 18.104.22.168.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).
I like to ensure that there is not active trx in the rollback segment
prior to shrinking it ...
r.name, l.Sid, p.spid,
NVL(p.username, 'no transaction') "Transaction",
l.Sid = p.pid (+)
AND TRUNC(l.id1(+) / 65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY r.name;
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