Re: Can't drop an unused roll back segment...
- From: "Leyi Zhang (Kamus)" <kamusis@xxxxxxxxx>
- To: s.cislaghi@xxxxxxxxx
- Date: Fri, 14 May 2010 19:19:29 +0800
If some error used to occurred in your UNDOTBS1, maybe the rollback segment
in UNDOTBS1 will always contain some active transactions, and you will never
drop UNDOTBS1 successfully by running normal commands, like Ste already
said.
But you can do this, run as below on your own risk :-)
1. change undo _management from AUTO to MANUAL
2. Add line as below to your initial parameter file:
_OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU1$)
3. Restart database instance
4. Drop rollback segment "_SYSSMU1$"
5. Drop tablespace UNDOTBS1
6. change undo _management from MANUAL back to AUTO
7. remove line which added in the step 2.
7. restart database instance
--
Kamus <kamusis@xxxxxxxxx>
Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org
On Fri, May 14, 2010 at 4:37 AM, Stefano <s.cislaghi@xxxxxxxxx> wrote:
> Guy,
>
> U've sent four times the same email that seems to be a bit unfinished at
> the end.
>
> BTW u can't drop an active undo tablespace in that way. Do this:
>
> 1. Create a new undo tablespace
> 2. Change in the spfile the undo tablespace with the new one
> 3. Make a shutdown consistent (so not abort!)
> 4. Start your database
> 5. Monitor if old undo tbs has emptied all segments
> 6. Drop the old undo tbs and its datafile
>
>
> Ste
>
> --
> http://www.stefanocislaghi.eu
>
> Sent by BlackBerry® Bold 9700
> ------------------------------
> *From: * patrick obrien <po04541@xxxxxxxxx>
> *Date: *Thu, 13 May 2010 12:41:20 -0700 (PDT)
> *To: *Oracle L<oracle-l@xxxxxxxxxxxxx>
> *Subject: *Can't drop an unused roll back segment...
>
> Oracle Admin, (sorry starting a new thread on this but its a bit different
> from the original)
>
> I'm trying to drop this unused undo tablespace/dbf file. Any thoughts would
> be greatly appreciated. Patrick OBrien.
>
> SQL> show parameter undo
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> undo_management string AUTO
> undo_retention integer 14400
> undo_tablespace string UNDOTBS3
>
> SQL> drop tablespace UNDOTBS1 including contents;
> drop tablespace UNDOTBS1 including contents
> *
> ERROR at line 1:
> ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
> tablespace
>
>
> SQL> drop rollback segment "_SYSSMU1$";
> drop rollback segment "_SYSSMU1$"
> *
> ERROR at line 1:
> ORA-30025: DROP segment '_SYSSMU1$' (in undo tablespace) not allowed
>
>
>
>
>
Other related posts:
