Changing undo tablespace online without database shutdown.

SQL> select name from v$database;

NAME
---------
SESDB


SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
SEARCH_ESS
SEARCH_DATA
SEARCH_DATA
SEARCH_DATA
SEARCH_INDEX
SEARCH_MDS

10 rows selected.

SQL> create undo tablespace undotbs2 datafile 'E:\app\SES\oradata\sesdb\undotbs0
2.dbf' size 1000m;

Tablespace created.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
UNDOTBS2
SEARCH_ESS
SEARCH_DATA
SEARCH_DATA
SEARCH_DATA
SEARCH_INDEX
SEARCH_MDS

11 rows selected.

SQL> alter system set undo_tablespace= undotbs2;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL>
SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
UNDOTBS2
SEARCH_ESS
SEARCH_DATA
SEARCH_DATA
SEARCH_DATA
SEARCH_INDEX
SEARCH_MDS

10 rows selected.

SQL>