Rollback Full

[abutler]

Hi Guys - I need your help again!

I'm confused! We have a rollback seg (rs_big) which is showing in Enterprise Manager under status as FULL, although under dba_segments it just shows online - how do I clear a rb segment? I thought once everything was commited that the space became availabe again anyway?? confused or what!

If I try to use rs_big I get the error
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 1
ORA-01562: failed to extend rollback segment number 1

Please help I don't know what to do.

Thanks Ali

[Mr.Hanky]

I think you are out of tablespace for RBS, this usually happens to me when the developers are running large batch jobs. Increase the tablespace for RBS.

Another possible cause is that you have hit maxextents for the RBS.

In enterprise manager you can manually shrink the RBS by right clicking on it.

It should start to shrink down once everything is committed, what is the optimal size set at?

MH

[abutler]

I tried the shrinking option to optimal size and this did nothing. The optimal size is 102400.

Excuse my ignorance but why do rbs' run out of tablespace? I thought that once the rows had been committed then it would be clean again and be re-used is this not correct???

How can you tell if all the rows have been committed, can you view something? and why doesn't the dba_segments show the status as being full?

Still new to all of this as you can probably tell!!

Thanks, Ali

[Mr.Hanky]

Well there is your problem, your optimal size is 102 megabytes, so it will not shrink beyond that point.

Often if there is a large job going on your rollbacks can exceed the allocated tablespace. I have had this problem when developers do not code commits in their programs, I di no think this is the problem you are having.

I am not sure about viewing the commits, one way would be to "SELECT" from the table, check the rowcount of check for the new data.

MH