Re: creating rollback segment

From: kev <kevin.porter_at_fast.no>
Date: 2000/02/17
Message-ID: <38AC2551.5D4A279@fast.no>#1/1

Sybrand Bakker wrote:

> They need to be in any tablespace other than SYSTEM, usually a separate
> tablespace, conventionally called RBS.

I appear to have an RBS tablespace already - "select * from v$tablespace" gives:

TS# NAME

---------- ------------------------------
         0 SYSTEM
         1 OEM_REPOSITORY
         2 RBS
         3 TEMP
         4 USERS
         5 INDX
         6 DRSYS

"select * from v$rollstat;" gives:

USN        EXTENTS    RSSIZE     WRITES     XACTS      GETS       WAITS
OPTSIZE    HWMSIZE    SHRINKS    WRAPS      EXTENDS    AVESHRINK  AVEACTIVE
STATUS          CUREXT     CURBLK
---------- ---------- ---------- ---------- ---------- ---------- ----------

---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------

         0         10     610304    1551656          0       4727
0                610304          0         31          0          0      61624
ONLINE                   4         13

"select * from v$rollname;" gives:

USN NAME

---------- ------------------------------
         0 SYSTEM

So I guess that means I currently have one rollback segment which is in the system tablespace.

>
> There needs to be one rollback segment per 4 users, with a minimum of 4.
> Then, my rule of thumb is:
> have 128M available for rollback segments
> make 4 rollback segments, initial extent 1 M next extent 1 M
> start with 4 extents and make sure they reach 32 M max) (Ie in my setup one
> rollbacksegment can't spoil it for the others)

My best guess for a command to do this job, at the moment, is:

create public rollback segment r01
tablespace rbs
storage (
initial 1M next 1M minextents 2
);

(and repeat for r02, r03, r04)

Anything I should add to that? (what exactly did "start with 4 extents and make sure they reach 32 M max" mean?)

>
> I always have a relatively small maintenance rollback segment, usually
> called rbstemp in the system tablespace. This make sure when the tablespace
> goes gaga, I can at least do something.
> Finally, I always use private rollback segments, as the init.ora allows me
> to choose which rollback segments are available (this is in order to make
> sure the maintenance segment is online in special cases only).
>

Does it matter whether I make these RBSs public or private?

thanks,

>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> kev <kevin.porter_at_fast.no> wrote in message
> news:38ABF910.D636271A_at_fast.no...
> > Hi,
> >
> > I'm trying to import some data (everything belonging to user1) from a
> > remote database to my local machine. My local DB was created with
> > dbassist immediately after installing Oracle 8i.
> > I've exported successfully on the remote machine, now when I try and do
> > an import, I get this error (for every table) and no data is imported:
> >
> > IMP-00058: ORACLE error 1552 encountered
> > ORA-01552: cannot use system rollback segment for non-system tablespace
> > 'USERS'
> > . . importing table "POLL_COUN_USA"
> >
> > I have been told that I must create public rollback segments and have
> > them online. I know very little about this sort of DBA activity. I need
> > advice on how to create these rollback segments and get the data
> > imported.
> > I know the syntax of the command, but don't know, eg, which tablespace I
> > should create them in, or how many I should create, or what name to give
> > them, or what size extents, etc. I really need my hand holding here, I'm
> > out of my depth but need to get this data reconstructed.
> >
> > If it's important, here's how I created the user on my local machine:
> >
> > create user user1 identified by passwd
> > default tablespace users
> > temporary tablespace temp
> > quota unlimited on users;
> >
> >
> > Can you help me?
> >
> > thanks,
> >
> > - Kev
> >
Received on Thu Feb 17 2000 - 00:00:00 CST