Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unable to take rollback segment offline

Re: Unable to take rollback segment offline

From: Bill Wagman <>
Date: Thu, 08 Jun 2000 13:50:29 -0700
Message-Id: <>

Winnie et al,

Running this script (and others I have used) show no transactions against the rollback segment which I am not able to take offline. I'm still quite puzzled.

At 12:12 PM 6/8/00 -0800, wrote:
>It is mostly because someone is still using that rollback segments. Use
>this query to determine who is currently using it.
>select a.xidusn "USN:RBS", "RBS", a.start_time "START_TIME",
>b.sid "SID",b.username "USERNAME",b.osuser "OSUSERNAME",
>b.program "PROGRAM RUNNING",b.status "STATUS"
>from v$transaction a,v$session b,
>v$rollname c where a.ses_addr=b.saddr and a.xidusn=c.usn
> >Bill Wagman <> on 06/08/2000 09:47:29 AM
> >
> >Please respond to
> >
> >To: Multiple recipients of list ORACLE-L <>
> >cc: (bcc: Winnie Liu/HQ/ISC)
> >
> >
> >
> >
> >
> >Hello,
> >
> >In a database used for a database class it became necessary to restructure
> >the rollback tablespace and segments. I started off with a 100MB rollback
> >tablespace with 10 rollback segments, all equally sized. I first increased
> >the size of the rollback tablespace to 600MB and then resized rollback
> >segment r01 so it could use the entire tablespace. I then attempted to
> >rollback segments r02 through r10 offline and drop them. All worked fine
> >except for one rollback segment, r08. In server manager I issue the
> >alter rollback segment r08 offline; and server manager replies the
> >statement is processed but when I query dba_rollback_segs r08 is still
> >shown as online. As far as I can determine (using suggestions in Kevin
> >Loney's book of SQL and PL/SQL scripts, other scripts appreciated) there
> >are no active transactions using rollback segment r08. When I attempt to
> >drop rollback segment r08 I receive ORA-01545: rollback segment r08
> >specified not available. The Enterprise Manager reports it being offline
> >I view all the rollback segments in the storage manager window but if I
> >look specifically at rollback segment r08, again in OEM, it reports it
> >being online. Oracle support says there are two undocumented parameters
> >which must be set, and they will only do it with you over the phone
> >(wonderful if they won't answer the phone any more) and won't pass out the
> >documentation as the risk of corrupting the database exists. Because the
> >database is actively in use at this time, a project is due shortly, I
> >find a time immediately to bounce the database and Oracle said I should be
> >concerned about it not coming up anyway.
> >
> >So... has anyone seen this problem and if so any advice, suggestions, help
> >would be most appreciated.
> >
> >Thanks.
> >
> >Bill Wagman
> >Univ. of California at Davis
> >Information Resources
> >
> >(530) 754-6208

Bill Wagman
Univ. of California at Davis
Information Resources
(530) 754-6208

Why is it that when you're driving and looking for an address, you turn Received on Thu Jun 08 2000 - 15:50:29 CDT

Original text of this message