Re: No Rollback Segments in Database

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 10 Oct 2002 22:21:57 -0700
Message-ID: <92eeeff0.0210102121.569d7088@posting.google.com>

> > 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.
>
> create public rollback segment temp;
> alter rollback segment temp online;
> create public rollback SEGMENT r01 TABLESPACE RBS;
> alter rollback segment temp offline;
> drop public rollback segment temp;

Oracle documentation is cryptic when it comes to ORA-01552. I know that you had to create a TEMP segment in system tablespace before creating other non-system rollback segments in 7.3.4.x. I think this workaround has been a non issue since 8.x as long as tablespace is dictionary managed. However, it is still required on locally managed tablespace. I have created a few 9.2 instances recently on Win2K and AIX using scripts from dbca and did not have to use this workaround.  

Here is a test script I just ran on 8.1.7.3 (Win2K). Default is dictionary managed tablespace.

<test2.sql script>
CONNECT internal/manager
STARTUP NOMOUNT PFILE=C:\oracle\ora817\oradata\TEST2\pfile\init.ora CREATE DATABASE TEST2
LOGFILE 'C:\oracle\ora817\oradata\TEST2\redologs\Redolog01.ora' SIZE 400K,

        'C:\oracle\ora817\oradata\TEST2\redologs\Redolog02.ora' SIZE 400K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'C:\oracle\ora817\oradata\TEST2\SYSTEM01.DBF' SIZE 50M REUSE MAXDATAFILES 1024
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET WE8ISO8859P1; CREATE TABLESPACE ROLLBACK
DATAFILE 'C:\oracle\ora817\oradata\TEST2\ROLLBACK01.DBF' SIZE 10M;

CREATE PUBLIC ROLLBACK SEGMENT RB1 TABLESPACE ROLLBACK STORAGE (OPTIMAL 4096K);
ALTER ROLLBACK SEGMENT "RB1" ONLINE; <Command line output>
SVRMGR> @C:\oracle\ora817\oradata\TEST2\temp\test2.sql Connected.
ORACLE instance started.

Total System Global Area                         94742556 bytes
Fixed Size                                          75804 bytes
Variable Size                                    74014720 bytes
Database Buffers                                 20480000 bytes
Redo Buffers                                       172032 bytes
Statement processed.
Statement processed.

Statement processed.
Statement processed.

> > 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.
>

This is correct.

Regards
/Rauf Sarwar Received on Fri Oct 11 2002 - 00:21:57 CDT