UNDO表空间切换步骤

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;