create undo tablespace UNDOTBS2 datafile '/data01/testdb/undotbs01.dbf' size 1G;

alter database datafile '/data01/testdb/undotbs01.dbf' resize 30g;

alter tablespace UNDOTBS2 add datafile '/data01/testdb/undotbs02.dbf' size 1g;

alter system set undo_tablespace = UNDOTBS2;

select tablespace_name , status , count(*) from dba_rollback_segs WHERE TABLESPACE_NAME='UNDOTBS1' group by tablespace_name , status;

  SELECT s.sid,

         s.serial#,

         S.STATUS,

         S.USERNAME,

         S.MODULE,

         S.MACHINE,

         s.sql_id,

         R.TABLESPACE_NAME,

         v.usn,

         segment_name,

         r.status,

         v.rssize / 1024 / 1024 mb

    FROM dba_rollback_segs r,

         v$rollstat v,

         v$transaction t,

         v$session s

   WHERE     r.segment_id = v.usn

         AND v.usn = t.xidusn

         AND t.addr = s.taddr

         AND TABLESPACE_NAME = 'UNDOTBS1'

ORDER BY segment_name;

Drop tablespace UNDOTBS1 including contents and datafiles;

losf|grep  delete

SELECT S.SID,s.serial#,S.USERNAME,S.STATUS,S.MODULE,S.MACHINE FROM V$SESSION S,V$PROCESS P WHERE S.PADDR=P.ADDR AND P.SPID=41487;