Advance Query

[sumit]

Hello All,

There is a tablespace RBSTBLSPACE. The size of the tablespace is 100M. Autoextend is off.

I have associated 2 rollback segment with that. RB1 and RB2.
I also have set an Optimal value.

I have executed a long statement. Suppose that has been associated with RB1.

My query is,

1) if the statement occupies all the blocks till optimal size ... and the statement yet to complete , what will happen? RB1 rollback segment still grow untill the full tablespace size has been occupied?

2) If it grows and occupies the total tablespace size , but the statement yet to finish, then what will happen? Will the whole statement rollback or it will again start occuping the first block in the tablespace?

An early response will be very much appreciated.

Thanks and regards

Sumit

[pando]

1. rbs will eats up the whole tablespace
2. rolls back the transaction

[marist89]

Originally posted by sumit
I have executed a long statement. Suppose that has been associated with RB1.

1. depends on what you mean by this. If it is an UPDATE, DELETE, or INSERT statement, the previous post was correct. If it is a SELECT statement, the RBS will stay the same size until oracle can no longer get a consistent view of the data. Once that happens, you will get an ORA-1555.