A question about RBS

From: Guang Mei <gmei_at_incyte.com>
Date: Thu, 4 Nov 2004 16:24:22 -0500
Message-ID: <NJEDKDKJDGAKAEKKNEEJCEFPDMAA.gmei@incyte.com>

Hi:

Oralce 8173 on Sun box. RBSLMT size is 2G.

First I got this when a delete statement was run. There was no any other transactions running during the whole thing.

ORA-1650: unable to extend rollback segment RBS007 by 16 in tablespace RBSLMT

Then I found:

select segment_name, bytes from dba_segments where segment_name like 'RBS%';

SEGMENT_NAME                          BYTES


-------------------------------- ----------

RBS001                            119537664
RBS002                             96862208
RBS003                            237633536
RBS004                             89653248
RBS005                            762052608
RBS006                            371982336
RBS007                            354549760
RBS008                            115081216

8 rows selected.

select sum(bytes) from dba_segments where segment_name like 'RBS%';

SUM(BYTES)

---

2147352576

Now I did the delete again (use the largest rbs from above info):

SQL> set transaction use rollback segment RBS005;

Transaction set.

SQL> delete from termhistory where historyid=19;

915798 rows deleted.

SQL> commit;

Commit complete.

My question is why the "delete" worked this time? I thought one RBS will take over other RBS's space automatically if necessary. Since there is no other transactions going on, which ever RBS (picked up by the tsansaction) should not matter.

Thanks.

Guang

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 04 2004 - 16:19:47 CST