Cacher is the code snippet organizer for pro developers

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
  SELECT segment_name
  FROM dba_segments 
  WHERE tablespace_name = 'UNDOTBS1'
);

create undo tablespace UNDOTBS2 datafile
'+FLASH' size 5G autoextend on, '+FLASH' size 5G autoextend on,
'+FLASH' size 5G autoextend on, '+FLASH' size 5G autoextend on;

alter system set undo_tablespace = 'UNDOTBS2' scope=both;
drop tablespace undotbs1 including contents and datafiles;