Re: UNDO TABLESPACE

1. Create another Undo Tablespace (CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ...)
2. Switch to that Tablespace (ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2' {with SCOPE=BOTH if using an spfile} or manually edit the init.ora to set UNDO_TABLESPACE, else the next restart will error out if UNDOTBS1 has been dropped but init.ora still points to UNDOTBS1)
3. Wait for a decent interval (eg as long as UNDO_RETENTION)
4. Take the old one offline and drop it (ALTER TABLESPACE UNDOTBS1 OFFLINE; DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES). Note however, that any sessions still referencing that Undo tablespace *will* get errors after that (I use "fuser" to see if any processes still have a read on the datafiles of that tablespace)

At 10:37 AM Thursday, Dean Paul wrote:
>Hi,
>
>I want to drop undo tablespace.
>and recreate it.What would be best way?
>Thanks
>
>_________________________________________________________________
>Fixing up the home? Live Search can help
>http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&locale=en-US&source=hmemailtaglinenov06&FORM=WLMTAG
>
>--
>http://www.freelists.org/webpage/oracle-l
>

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l