Please help. Two questions about Rollback Segment.

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 - 21:17:04 CST