Change or switch UNDO tablespace

Change or switch UNDO tablespace in Oracle database and issues of switching UNDO Tablespace (How to increase size of UNDO tablespace?):

Before I proceed with the topic, I would like to have an overview first. The Undo tablespace is used for several purposes: ROLLBACK, READ CONSISTENCY and FLASHBACK technology.

I have already discussed about Undo Tablespace in my earlier post and if you want to flashback, here is the URL:

http://appsdbaraz.blogspot.com/2013/12/tablespaces.html

It is very important to know, how to switch Undo Tablesspaces as it is required when we need to increase the size of undo tablespace. But sometimes, we face issues if we try to drop the old undo tablespace after switching to new undo tablespace and it says "Tablespace is in use". This is because of  pending transactions in your old undo tablespace and get "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.

How to switch to a new UNDO tablespace and drop the old one in oracle database?

$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS01

SQL>

We can see the current undo tablespace as UNDOTBS01 and its displayed by initialization parameter undo_tablespace. Leave this session as its and cosider it as session1.

Start a new session (session2) and log in as user SCOTT and initiate a transaction.

$ sqlplus scott/tiger

SQL>update emp1 set salary = salary + 1000 where empno=83795;

1 row updated.

We have started a transaction by updating on emp1 table and undo data is written to a segment in the UNDOTBS01 tablespace. Now leave session2 as it is and go to session2 as sysdba console. Just to notice, We have not issued COMMIT or ROLLBACK,

UNDOTBS02 is a new UNDO tablespace created:

SQL> CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/u01/apps/oracle/db/UNDOTBS02.dbf'

         SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

Switching the database to the new UNDO tablespace UNDOTBS02:

SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH;

System altered.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS01' is currently in use

-- Try to drop the tablespace but failed.

SQL>

We have chaged the undo tablespace to UNDOTBS02 with alter system set undo_tablespace=UNDOTBS02 and any new transaction will go into new undo tablespace i.e. UNDOTBS02. But undo transaction for already pending transaction(with session2 - commit or rollback was not issued) is still in the old undo tablespace with status as PENDING OFFLINE. Until or unless those data are there, it is not possible to drop the existing tablespace.

We can get the status of undo segment and name of UNDOTBS01 tablespace from rollname, rollstat , dba_segments tables.

We can get the SID and SERIAL# and go to the user and request to end the transaction by issuing ROLLBACK or COMMIT. If in case this is not possible (user will not be available for long time), we may go ahead and kill the session to release the old UNDO segment in UNDOTBS01 tablespace.

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

Now we can go ahead and drop UNDOTBS01 tablespace.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS01' is currently in use

If UNDO_RETENTION time is not exceeded, then we will get above error and need to wait till the UNDO_RETENTION time is over. After that we can drop this UNDO tablespace easily. By default UNDO_RETENTION is 15 minutes or 900 seconds.

SQL> DROP TABLESPACE UNDOTBS01 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped