| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rollback Segment Trans
Hi,
Check v$rollstat which has a column XACTS which shows numbers of transactions using that perticular rollback segments. Plus you can check v$transaction. It has a column XIDUSN. It shows which rollback segment that perticular transaction is using. As you said you killed a long running delete statement. Check USED_UBLK column of v$transaction for that perticular transaction. If the value is decreasing contiously, then PMON is still rolling back your statement. Once PMON is done with it, you can shrink the rollback segment.
~Dilip
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Fahd Mirza
Sent: Wednesday, April 07, 2004 9:45 AM
To: oracle-l_at_freelists.org
Subject: Rollback Segment Trans
Hi experts,
I just killed a very long session which wasn't responding and was inactive for 2 days.
The session was performing a delete operation on a table. After I killed the session, I noticed that the size of one rollback segment became very large, the optimal value is 4M and the current size of the particular rollback segment is 500M. I have tried to shrink rollback segment manually like this
alter rollback segment rbs25 shrink to 4M;
but in vain.
what should I do?
how could it be known that a particular rollback segment is using currently which transactions?
Thanks in advance,
regards.
Fahd
 |
 |