SUMMARY: How to Drop a PUBLIC ROLLBACK SEGMENT
Date: Tue, 27 Oct 1992 19:08:04 GMT
Message-ID: <1992Oct27.190804.24592_at_lmpsbbs.comm.mot.com>
Original Question
> Subject: Trouble Dropping a PUBLIC ROLLBACK SEGMENT
>
> I have a system from which I wish to drop a PUBLIC ROLLBACK SEGMENT.
> If I read the DBA manual, I am supposed to shut the database down and
> restart it in an EXCLUSIVE mode. At this point the STATUS of the
> ROLLBACK SEGMENT is supposed to be available, so it can be DROPPED.
>
> Problem is that it still shows as being IN USE. Taking the ROLLBACK
> SEGMENT out of the init file does not make any difference.
>
> If I create a ROLLBACK SEGMENT not declaring it as PUBLIC, this
> all works as described.
>
> The question is how can I drop a ROLLBACK SEGMENT that was
> originally declared as PUBLIC ??
>
Responses Received.
thanks to both of the responses. With both I managed to resolve
the rollback problems.
From: John Peach Internet: epeas_at_abds7.aberdeen.chevron.com
> With great difficulty!
>
> Oracle recommend you don't user PUBLIC rollback segments because of
> this....
>
> The ONLY way to drop it is to keep connecting/disconnecting until
> Oracle decides not to use it.
Note: I tried this. It works sometimes, but can take a while. Also note that the Oracle DBA manual is "less than clear" about this issue.
From: DBA FAQ
>
> by Kevin M. Loney, aka kml_at_cellar.org (username "Scott Tiger")
>
> Third in a Series.
>
> Part 3. Rollback Segment Management
>
> Dropping Public Rollback Segments
> =================================
> <stuff deleted>
> In order to drop a public rollback segment, you must first convince
> Oracle that it doesn't need it. To do this, rig init.ora so that
> Oracle starts up using just the SYSTEM rollback segment. Add the
> following lines to init.ora (if there aren't already entries for
> these variables):
>
> transactions = 20
> transactions_per_rollback_segment = 20
>
> and start the database. You will now be able to drop the public
> rollback segments. Do yourself a favor and change them to private ones.
> When you're done your work, change your init.ora back to its earlier
> values.
>
This works better, but it also does not work 100 percent of the time.
I found that the number of transactions set MUST !!! match the
transactions_per_rollback_segment.
Even then the database may have to be brought up and down a couple of
times.
Thanks to all who responded.
-- +------------------------------------------------------------------+ | Garth Kennedy Internet: garth_at_comm.mot.com | | Shared Systems/Component Products | | Factory Systems Internal Mail: IL02-2317 |Received on Tue Oct 27 1992 - 20:08:04 CET