Recreate Undo Tablespace In Oracle Database

In this post we are going to show how to recreate undo tablespace for oracle database 11g/12c.

What is undo tablespace:

In oracle database need some permanent tablespace undo tablespace is one of them which tablespace is used by oracle database engine to manage undo data when database is running in automatic undo management mode.

There are some uses of undo records or data are given below:
1. Rollback transactions when a rollback statement is issued
2. Provide read consistency
3. Recover the database
4. Analyze the data from an earlier point in time by using oracle flashback query
5. Recover from logical corruptions using Oracle Flashback features

Why need to recreate undo tablespace?

Sometime comes a situation, that undo tablespace size has become very high and we are unable to reclaim it and in this situation we need to create a new undo tablespace with appropiate size and drop the old undo tablespace. Undo tablespace recreate steps are given below.

Step 1: Check undo tablespace details

Login as sysdba
sqlplus / as sysdba
SQL> show parameter undo
 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

Step 2: Check the existing undo tablespace location

SQL> select file_name from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME
--------------------------------------------------------------------------------
E:\APP\MASUD\VIRTUAL\ORADATA\TEST\UNDOTBS01.DBF

Step 3: Create new undo tablespace with appropiate size

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'E:\APP\MASUD\VIRTUAL\ORADATA\TEST\UNDOTBS02.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE 30G;

Tablespace created.

Step 4: Undate undo_tablespace parameter

SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;

System altered.

@ Check undo parameter
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

Note: Now new transaction will start using newly create undo tablespace (UNDOTBS2).

Step 5. Check for the active rollback segment in old tablespace using below query

SQL> set pagesize 200
SQL> set lines 200
SQL> set long 999
SQL> col username for a9
SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#
  2  FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
  3  WHERE  a.usn = b.usn
  4  AND    a.usn = c.xidusn
  5  AND    c.ses_addr = d.saddr
  6  AND    a.name IN (
  7    SELECT segment_name
  8    FROM dba_segments
  9   WHERE tablespace_name = 'UNDOTBS1'
 10  );

no rows selected

if session is exist which are using old undo tablespace we can kill the session using below command

SQL> alter system kill session 'SID,SERIAL#' immediate;

Step 6: Drop old undo tablespace

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Note: Befor old undo tablespace drop we must kill session which are still using old undo tablespace (find from step 5) or either we can ask end users to commit or rollback their sessions or have to wait till time undo_retention period, then we can drop old undo tablespace.