Deleting UNDOTBS

[TomNJerry]

I already tried setting undo_retention=1 and restarting the DB. But doesnot work.



SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- -------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1




SQL> select * from dba_tablespaces where tablespace_name='UNDOTBS';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ----------
ALLOCATIO PLU SEGMEN
--------- --- ------
UNDOTBS 4096 65536 1
2147483645 65536 ONLINE UNDO LOGGING LOCAL
SYSTEM NO MANUAL



SQL> select * from dba_data_files where tablespace_name='UNDOTBS';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
E:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF
2 UNDOTBS 6485442560 1583360 AVAILABLE
2 YES 8388608000 2048000 1280 6485377024 1583344

D:\UNDOTBS\UNDOTBS02.DBF
13 UNDOTBS 1048576000 256000 AVAILABLE
13 NO 0 0 0 1048510464 255984

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------

D:\UNDOTBS\UNDOTBS03.DBF
14 UNDOTBS 1048576000 256000 AVAILABLE
14 NO 0 0 0 1048510464 255984

D:\UNDOTBS\UNDOTBS04.DBF
15 UNDOTBS 1048576000 256000 AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
15 NO 0 0 0 1048510464 255984




SQL> SELECT STATUS,RELATIVE_FNO,TABLESPACE_NAME,COUNT(*) FROM DBA_ROLLBACK_SEGS GROUP BY STATUS,RELATIVE_FNO,TABLESPACE_NAME;

STATUS RELATIVE_FNO TABLESPACE_NAME COUNT(*)
---------------- ------------ ------------------------------ ----------
OFFLINE 2 UNDOTBS 4
ONLINE 1 SYSTEM 1
ONLINE 16 UNDOTBS1 10
PARTLY AVAILABLE 2 UNDOTBS 1




If you notice in the last query one rollback segment is partly available, which
is not allowing to drop that tablespace. So I tried this:


SQL> alter session set undo_suppress_errors=TRUE;
Session altered.

SQL> alter rollback segment "_SYSSMU6$" offline;
Rollback segment altered.

Then tried dropping the tablespace; But in vain.