Dropping undo & Creating New UNDO

Old ---undo
New----UNDOTBS_NEW


SQL> select  TABLESPACE_NAME,FILE_NAME from dba_data_files where tablespace_name ='undo';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS10.DBF

UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS11.DBF

UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS12.DBF


3 rows selected.

SQL> create undo tablespace UNDOTBS_NEW datafile 'E:\MUSAIB\DATA\UNDOTBS151.DBF' size 30G reuse autoextend off;

Tablespace created.

SQL> alter tablespace UNDOTBS_NEW add datafile 'E:\MUSAIB\DATA\UNDOTBS152.DBF' size 30G reuse autoextend off;

Tablespace altered.

SQL>
SQL> alter system set undo_tablespace=UNDOTBS_NEW;

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      UNDOTBS_NEW


SQL> drop tablespace undo including contents and datafiles;

Tablespace dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

k:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 21 09:23:04 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
MUSAIB     READ WRITE

SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%UNDO%';

SUM(BYTES/1024/1024/1024)
-------------------------
                       60

SQL>