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