Help: Problem with rollback segment

From: sirirut vanichayobon <svanicha_at_cs.ou.edu>
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