Oracle Database Discussions

Hello Gurus,

Undo tablespace is growing excessively and we want to drop it and create a new one. Below are the outlined steps to achieve that. Kindly review and advise us accordingly.


----BACKUP the UNDOTBS1 tablespace

rman>BACKUP tablespace UNDOTBS1

---create the tablesspace UNDOTBS2

sql>create the tablesspace UNDOTBS2 DATAFILE '/UBSUNDO/ghadb_undotbs_029.dbf' SIZE 2G;


---set the tablespace UNDOTBS2 to the default

sql>alter system set undo_tablespace=UNDOTBS2;

---shutdown cleanly,startup and shutdwon cleanly again. Then startup

sql> shutdown immediate;

sql> startup;

sql>shutdown immediate;

sql> startup;

--- show parameter to confirm that the UNDOTBS2 is being used by the database.
==========
sql>show parameter undo;


---drop tablespace UNDOTBS1 including contents and datafiles;
note
----
----if UNDOTBS1 fails to drop

sql>ALTER TABLESPACE UNDOTBS1 OFFLINE NORMAL;

----then drop it

sql>drop tablespace UNDOTBS1 including contents and datafiles;


Thank you.