Re: Datafile for rollback segment

Hi Jayarman,

I performed this test on 9.2 (not 8.1.7 I know).

SQL> create tablespace rbs
  2 datafile 'c:\Bowie\rbs01.dbf' size 1m, 'c:\Bowie\rbs02.dbf' size 1m   3 uniform size 64k;

Tablespace created.

SQL> alter database datafile 'c:\Bowie\rbs01.dbf' offline;

Database altered.

SQL> create rollback segment rbs01
  2 tablespace rbs
  3 storage (initial 64K next 64K minextents 10);

Rollback segment created.

SQL> create rollback segment rbs02
  2 tablespace rbs
  3 storage (initial 64K next 64K minextents 20); create rollback segment rbs02
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 5 in tablespace RBS

So I had no problems creating a rollback segment initially and failing to create one if there's insufficient space in the remaining data file.

As it should be.

However ...

If you already have rollback segments created and online in the tablespace, your database would be in all sorts of trouble. Why ? Because by taking the datafile offline portions of the rollback segments effectively become unusable causing transactions and the such to crash. And as the offline is effectively an offline immediate, Oracle doesn't attempt a checkpoint so the datafile can only be brought back via a recovery. And as it contains an active rollback segment, you can only perform such a recovery with a closed (mounted) database.

Can fully test this on a 8.1.7 database at the moment but I suspect this is what is causing the grief.

Make sense ?

Cheers

Richard

"Jayaraman Ashok" <ashok_jayaraman_at_yahoo.com> wrote in message news:7ca2852.0210081855.1df7c51c_at_posting.google.com...
> Hi HJR
>
> I did it purely for academic interest. Since when a tablespace has 2
> datafiles and if one of them is online, you are allowed to create a
> table on that tablespace, then why not the same for a rollback, as
> both are creating only segments. More clarification / explanation is
> requested from all.
>
> Cheers,
> Ashok
>
>
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:<k7xo9.48490$g9.138655_at_newsfeeds.bigpond.com>...
> > Can I just ask why you care where the rollback segment is physically
> > created?
> >
> > HJR
> >
> >
> > "Jayaraman Ashok" <ashok_jayaraman_at_yahoo.com> wrote in message
> > news:7ca2852.0210080027.22123f38_at_posting.google.com...
> > > Hi
> > > I have two datafiles for rollback segment tablespace and have enough
> > > free space on both the datafiles. Now to create a rollback segment in
> > > one specific datafile i offline the other datafile and try to create
> > > the rollback segment, which naturally must create it on the online
> > > datafile. But it is failing with ORA-3113 and i have to do media
> > > recovery to bring it back online. What is wrong here ? The setup is
> > > Oracle8.1.7.0.0 on Solaris7.
> > >
> > > Cheers,
> > > Ashok
Received on Wed Oct 09 2002 - 07:21:59 CDT