SQL> show parameter undo
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
CREATE UNDO TABLESPACE undotbs2
DATAFILE’/u01/app/oracle/oradata/orcl/undotbs02.dbf’ SIZE 50M AUTOEXTEND ON NEXT 5M;
Tablespace created.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.
SQL> show parameter undo ;
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
Now you need to check if there’s any segment used old undo tablespace to ensure you will not loose any Data
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs where tablespace_name like ‘%UND%’;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
_SYSSMU11$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU12$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU13$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU14$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU15$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU16$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU17$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU18$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU19$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU20$ PUBLIC UNDOTBS2 ONLINE
Make Sure that you don’t have any Segment Using Undo01/Old Undo Tablespace and if you have one wait until the transaction become Invalid Or expired.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
