Re: Please help. Two questions about Rollback Segment.

1. A transaction uses always one rollback segment, it will NEVER switch rollback segments if it runs out of space. Also, you created a PUBLIC rollback segment, while the rollback segments parameter in initSID.ora only handles PRIVATE rollback segments. Oracle itself determines which PUBLIC rollback segments go online. So, yes, if you want to be sure you should use set transaction. Normally, you would have multiple rollback segments (minimum 4) of the same sufficient size, so this issue occurs only rarely.
2. a set transaction statement needs to be the FIRST statement of a transaction. You need to either commit or rollback before you use a set transaction.

Hth,

--
Sybrand Bakker, Oracle DBA
<laulau823_at_my-deja.com> wrote in message news:83hine$f90$1_at_nnrp1.deja.com...
>
>
> I have two questions about Rollback segments:
>
> 1) I create a rollback segment as follows:
>
> SQLPLUS> create tablespace rbs2 datafile
> '/testing/rbs_tbs2.dbf' size 50M
> default storage
> (initial 120k
> next 120k
> pctincrease 0
> minextents 2
> );
> SQLPLUS> create public rollback segment r05 tablespace rbs2;
> SQLPLUS> alter rollback segment r05 online;
>
> However, I found that Oracle still using the old rollback segment, as a
> result, it shows an error message (something like not enough space in
> old rollback segment) when I do bulk insertion. I have already add r05
> in rollback_segments in initSID file (and shutdown and startup again).
> What can I do (apart from using "set transaction use rollback segment
> r05") such that the new rollback segment can automatically use when
> there is no enough space in the old rollback segment ?
>
> 2) I have a procedure to do dynamic SQL. The dynamic SQL is about to do
> data insertion. Before running this procedure in SQL*Plus, I type the
> following in SQL*Plus:
>
> set transaction use rollback segment r05;
>
> However, I found that the dynamic SQL not use the r05 segment. It still
> using the old rollback segment. As a result, Oracle shows an error
> about not enough space in the old rollback segment. What can I do such
> that the dynamic SQL can use a specific rollback segment?
>
> Thanks,
> David
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Dec 18 1999 - 23:24:00 CST