Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: A question about RBS

RE: A question about RBS

From: Ruth Gramolini <rgramolini_at_tax.state.vt.us>
Date: Fri, 5 Nov 2004 09:45:26 -0500
Message-ID: <003301c4c346$1627f160$8459000a@vttaxnet.tax.state.vt.us>


An Ora 1650 means that you don't have enought room in the tablespace: 01650, 00000, "unable to extend rollback segment %s by %s in tablespace %s"

// *Cause:  Failed to allocate an extent for rollback segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//         files to the tablespace indicated.

It doesn't matter whether it is rbs or undo, if there is no room, there is no room.

Ruth
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Paul Drake Sent: Thursday, November 04, 2004 4:41 PM To: gmei_at_incyte.com
Cc: Oracle-L-freelists
Subject: Re: A question about RBS

Guang.

8.1.7.3. you're thinking about UNDO tablespaces in 9.2 and later.

8.1.7:
eventually, if you have OPTIMAL set, the inividual rollback segments that are over their optimal setting will shrink. eventually. This means that they will use free (or "freed") extents in the tablespace, as in non-allocated.
This does not mean that they can de-allocate extents from a rollback segment.
Big difference.

9.2:
an undo segment can "steal" extents away from another undo segment. (notice how I did not say "rollback segment" here?)

Paul

(gratuitous reminder about Oracle Security Alert #68 and de-support of 8.1.7 as of 31-DEC-2004 - patch/upgrade/migrate or .. DIE ... (or not) exploit code to be released by NGS at the end of the month)

On Thu, 4 Nov 2004 16:24:22 -0500, Guang Mei <gmei_at_incyte.com> wrote:
> 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
>

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 05 2004 - 08:43:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US