Re: dropping a PUBLIC rollback seg

From: <sstephen_at_us.oracle.com>
Date: 25 Sep 92 07:15:40 GMT
Message-ID: <1992Sep24.231540.1_at_us.oracle.com>

In article <1992Sep23.162159.172_at_cpvax.cpses.tu.com>, b_lmcgilve_at_cpvax.cpses.tu.com writes:
> In article <1992Sep22.145815.336_at_condor.navsses.navy.mil>, system_at_condor.navsses.navy.mil writes:

>> I have a group of users who are new ORACLE on the VAX as well as new DBAs.  We
>> are running ORACLE for VAX/VMS 6.0.36 (6.2).  What the user wants to do is to
>> DROP a public rollback segment that is current in use, as well as the
>> tablespace that contains the rollback segment.  He has tried the DROP PUBLIC
>> ROLLBACK SEGMENT and DROP TABLESPACE and both commands returned the Rollback
>> segment in use error.  Is there a way to drop these rollback segments so he can
>> drop or take the tablespaces off line?

>
> Add a line to the init.ora file that says:
>
> _offline_rollback_segment = (rollback2)
>
> remove the name of the rollback segments from the list of rollback segments
>
> rollback_segments = (rollback1, rollback3)
>
> Take the database down and back up.
> Then you can drop the rollback segment and drop the tablespace.
>
> -----------------------------------------------------------------------
> Laurel McGilvery Not only do I not speak for the
> CPSES (temporarily) company, the company does not
> Bechtel Corporation speak for me.
>
> All opinions expressed are obviously mine. Who else would own up to them?

OOPS! Pressed exit too fast. There is a better and much safer solution :

INIT.ORA: transactions_per_rollback_segment = 100
transactions = 100

>> shutdown and startup

DROP PUBLIC ROLLBACK SEGMENT >> remove those two parameters
>> shutdown and startup

---

This solution hass always worked for me. The two INIT.ORA parameters tell the database to stop allocating rollback segments after it hits the ratio of those 2 numbers = 100/100 = 1. It will only allocate the SYSTEM rollback segment. Received on Fri Sep 25 1992 - 09:15:40 CEST