| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unable to take rollback segment offline
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, Winnie_Liu_at_infonet.com wrote:
>Bill,
>
>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", c.name "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
>
>WInnie
>
> >Bill Wagman <wjwagman_at_ucdavis.edu> on 06/08/2000 09:47:29 AM
> >
> >Please respond to ORACLE-L_at_fatcity.com
> >
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >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
>take
> >rollback segments r02 through r10 offline and drop them. All worked fine
> >except for one rollback segment, r08. In server manager I issue the
>command
> >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
>if
> >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
>can't
> >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
> >wjwagman_at_ucdavis.edu
> >(530) 754-6208
Bill Wagman
Univ. of California at Davis
Information Resources
wjwagman_at_ucdavis.edu
(530) 754-6208
 |
 |