Recreating and Issuing UNDO tablespace and reclaim the disk space.

Hi Guys,

Recreate UNDO looks very simple but a little focus is required while doing and re bouncing the database. Mainly here i am concentrating on claiming the space which occupied by UNDO tbs(23 GB). Here are the steps regarding that:

First here is my UNDO tablespace PATH and Parameter:

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     18000
undo_tablespace                      string      UNDOTBS1

SQL> select file_name from dba_data_files where tablespace_name='UNDOTBS1';

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/XE/undotbs1.dbf

Now create new UNDO tbs and alter system

SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/XE/undotbs2.dbf' size 500M autoextend on next 20M maxsize 10G;

Tablespace created.

SQL> alter system set undo_tablespace=UNDOTBS2;

System altered.

Now bring the undo_management to AUTO and bounce the database:

SQL> alter system set undo_management=MANUAL scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2233344 bytes

Variable Size            1019218944 bytes

Database Buffers           41943040 bytes

Redo Buffers                5541888 bytes

Database mounted.

Database opened.

Now cross check the changes:

SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      MANUAL

undo_retention                       integer     18000

undo_tablespace                      string      UNDOTBS2

Now drop the old UNDO tbs, alter undo_management=AUTO and then bounce the database:

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> alter system set undo_management=AUTO scope=SPFILE;

System altered.

SQL> shutdown immediate;

Database closed.

SQL> startup;

ORACLE instance started.

SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     18000

undo_tablespace                      string      UNDOTBS2

Thats is!, now i have claimed disk space back.

Thanks!