Ken, I know that one needs to wait for the pending transections before trying to drop a tablespace.
Also learnt that I should set the undo_retention parameter to very low and wait until those
transections are over and undo_retention is ignored id the transection is going to take more time than the
specified.

After some workaround I found that there are some active segments dated to 12th Dec 2003 in that undo tablespace.
So I set undo_retention to 5 secs, hanged on for a few minutes and restared the Db. But no outcome! Following
are the information that you wanted:

select NAME, TYPE, VALUE, ISDEFAULT,ISSES_MODIFIABLE, ISSYS_MODIFIABLE
ISMODIFIED,ISADJUSTED,DESCRIPTION from v$parameter where name like 'undo%'


NAME TYPE VALUE ISDEFAULT ISSES_MODIFIABLE ISMODIFIED ISADJUSTED
----------------------------------------------------------------------------------------------------------
undo_management 2 AUTO FALSE FALSE FALSE FALSE
undo_tablespace 2 UNDOTBS FALSE FALSE IMMEDIATE FALSE
undo_suppress_errors 1 FALSE TRUE TRUE IMMEDIATE FALSE
undo_retention 3 5 FALSE FALSE IMMEDIATE FALSE


SQL> SELECT a.name, b.status
2 FROM v$rollname a, v$rollstat b
3 WHERE a.name IN (SELECT segment_name
4 FROM dba_segments
5 WHERE tablespace_name = 'UNDOTBS')
6 AND a.usn = b.usn;

NAME STATUS
------------------------------ ----------
_SYSSMU10$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE



Following are the active segments:


select SEGMENT_NAME, TABLESPACE_NAME, STATUS, COMMIT_WTIME from DBA_UNDO_EXTENTS where status!='EXPIRED'

SEGMENT_NAME TABLESPACE_NAME STATUS COMMIT_WTIME
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:39
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:42
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:42
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:45
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:45
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:49
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:49
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:52
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:54
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:54
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:58
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:08:58
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:00
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:00
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:03
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:03
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:33
_SYSSMU6$ UNDOTBS ACTIVE 12/14/2003 23:09:33



Following are the no of bytes that is needed:

1* select SEGMENT_NAME, TABLESPACE_NAME, STATUS, COMMIT_WTIME from DBA_UNDO_EXTENTS where status!=
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) As "Bytes"
2 FROM (SELECT value As UR
3 FROM v$parameter
4 WHERE name = 'undo_retention'),
5 (SELECT (SUM(undoblks)/SUM(((end_time -
6 begin_time) * 86400))) As UPS
7 FROM v$undostat),
8 (SELECT value As DBS
9 FROM v$parameter
10 WHERE name = 'db_block_size');

Bytes
----------
98304


HOW DO I GET RIGD OF THESE SEGMENTS?