Help: Problem with rollback segment
Date: Tue, 14 Jul 1998 13:52:06 -0500
Message-ID: <35ABA8D6.F57C1F27_at_cs.ou.edu>
HI,
I have a problem about rollback segment. I always get the follwing messages after I populate the data into my table (by running PL/SQL to get data from one table and insert the data into another table).
ORA-01562: failed to extend rollback segment number 3 ORA-01650: unable to extend rollback segment R2 by 256 in tablespace RBS
After adding a file into my database this problem is solved. However, I will get this error again soon and then I have to add files again. I just want to know how much space I should add so I don't need to add more often. Can I reuse my space in rollback tablespac?
I also attach my script file which I used to create my database and rollback.
Any help would appreciate. Please clearify me about this topic. I did read from the DBA handbook but still have a confusion.
Thanks in advance,
-sirirut-
My script file
REM
REM Create the database.
REM spool /u01/app/oracle/admin/meso/create/crdb2meso.lst
connect internal
REM
REM Create additional rollback segment (r0) in SYSTEM tablespace before
REM creating other tablespaces.
REM
create rollback segment r0 tablespace system
storage (initial 32k
next 32k minextents 2 maxextents 50);
REM
REM Alter the new rollback segment to make it available.
REM Further tablespaces can then be created.
REM
alter rollback segment r0 online;
REM
REM Create a tablespace (RBS) for rollback segments.
REM
create tablespace rbs datafile
'/u02/oradata/meso/rbs01.dbf' size 50M
default storage ( initial 1M
next 1M pctincrease 0 minextents 9
maxextents 50);
REM
REM Create a tablespace (TEMP) for temporary segments.
REM Temporary tablespace configuration guidelines:
REM Initial and next extent sizes = k * SORT_AREA_SIZE, k in
{1,2,3,...}.
REM
create tablespace temp datafile
'/u02/oradata/meso/temp01.dbf' size 600M
default storage ( initial 1M
next 1M pctincrease 0);
REM
REM Create a TOOL tablespace for static table.
REM
create tablespace mesotool datafile
'/u02/oradata/meso/tool01.dbf' size 30M;
REM
REM Create a USER tablespace for static table.
REM
create tablespace mesouser datafile
'/u02/oradata/meso/user01.dbf' size 30M;
REM
REM Create an INDEX tablespace.
REM
create tablespace mesoindex_1 datafile
'/u03/oradata/meso/mesoindex11.dbf' size 1024M,
'/u03/oradata/meso/mesoindex12.dbf' size 1024M;
REM
REM Create an INDEX tablespace for static tables.
REM
REM
REM Create a DATA tablespace.
REM
create tablespace mesodata_1 datafile
'/u04/oradata/meso/mesodata11.dbf' size 1024M,
'/u04/oradata/meso/mesodata12.dbf' size 1024M;
REM
REM Create rollback segments.
REM
create rollback segment r1 tablespace rbs storage (optimal 9M);
create rollback segment r2 tablespace rbs storage (optimal 9M);
create rollback segment r3 tablespace rbs storage (optimal 9M);
create rollback segment r4 tablespace rbs storage (optimal 9M);
REM Deactivate the second rollback segment (r0) in the REM SYSTEM tablespace.
alter rollback segment r1 online; alter rollback segment r2 online; alter rollback segment r3 online; alter rollback segment r4 online;
alter rollback segment r0 offline; Received on Tue Jul 14 1998 - 20:52:06 CEST