Re: No Rollback Segments in Database

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 10 Oct 2002 16:09:51 +0100
Message-ID: <3da59840$0$1291$ed9e5944@reading.news.pipex.net>

"Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message news:92eeeff0.0210100618.3af45202_at_posting.google.com...
> > Shouldn't there always be at least one public rollback segment?
> No. You have to create them. There is only system rollback segment
> created at the time you create the database.

Which is correct.

>
> You can use the following command to create one to many segments.
> CREATE PUBLIC ROLLBACK SEGMENT RB1 TABLESPACE RBS
> STORAGE (OPTIMAL 4096K);
> ALTER ROLLBACK SEGMENT "RB1" ONLINE;
If you issue this command with only a system rollback segment you will get

ORA-01552 cannot use system rollback segment for non-system tablespace 'RBS'

You need to create a temporary rollback segemnt in the system tablespace to start with create your other rollback segs and then drop the temporary one.

eg

create public rollback segment temp;
alter rollback segment temp online;

create public rollback SEGMENT r01 TABLESPACE RBS;
create public rollback SEGMENT r02 TABLESPACE RBS;
create public rollback SEGMENT r03 TABLESPACE RBS;
create public rollback SEGMENT r04 TABLESPACE RBS;
create public rollback SEGMENT r05 TABLESPACE RBS;
create public rollback SEGMENT r06 TABLESPACE RBS;
create public rollback SEGMENT r07 TABLESPACE RBS;
create public rollback SEGMENT r08 TABLESPACE RBS;
create public rollback SEGMENT r09 TABLESPACE RBS;
create public rollback SEGMENT r10 TABLESPACE RBS;
create public rollback SEGMENT r11 TABLESPACE RBS;
create public rollback SEGMENT r12 TABLESPACE RBS;
create public rollback SEGMENT r13 TABLESPACE RBS;
create public rollback SEGMENT r14 TABLESPACE RBS;
Create public rollback SEGMENT r15 TABLESPACE RBS;
create public rollback SEGMENT r16 TABLESPACE RBS;
create public rollback SEGMENT r17 TABLESPACE RBS;
create public rollback SEGMENT r18 TABLESPACE RBS;
create public rollback SEGMENT r19 TABLESPACE RBS;
create public rollback SEGMENT r20 TABLESPACE RBS;
create public rollback SEGMENT r21 TABLESPACE RBS;
create public rollback SEGMENT r22 TABLESPACE RBS;
create public rollback SEGMENT r23 TABLESPACE RBS;
create public rollback SEGMENT r24 TABLESPACE RBS;
create public rollback SEGMENT r25 TABLESPACE RBS;
create public rollback SEGMENT r26 TABLESPACE RBS;
create public rollback SEGMENT r27 TABLESPACE RBS;
create public rollback SEGMENT r28 TABLESPACE RBS;
create public rollback SEGMENT r29 TABLESPACE RBS;
create public rollback SEGMENT r30 TABLESPACE RBS;
Create public rollback SEGMENT r31 TABLESPACE RBS;
create public rollback SEGMENT r32 TABLESPACE RBS;

alter rollback segment temp offline;
drop public rollback segment temp;

>
> After creating these segments, specify them in init.ora file as
> rollback_segments = (RB1, RB2, ....) so that they are online on next
> database startup.

These are for private not public rollback segs IIRC.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Thu Oct 10 2002 - 10:09:51 CDT