Undo Tablespace

To check the available undo tablespaces:

select tablespace_name, contents, status from dba_tablespaces;

select tablespace_name, contents, status from dba_tablespaces where contents = 'UNDO';

To check its size:

select tablespace_name, file_name, bytes/1024/1024 "Mb"
from dba_data_files 
where tablespace_name in('UNDOTBS1');

To see the parameters related to undo:

SQL> show parameter undo

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

Here undo_retention is 900 seconds, which means 15 minutes.  To make it one hour set the below parameter: this is a dynamic parameter.

alter system set undo_retention = 3600 scope = both;

As only one data file in undo tablespace, it may cause contention. Better more than one datafile under an undo tablespace. We can add one more datafile to undotbs1.

Adding a datafile to an existing UNDO tablespace

alter tablespace undotbs1
add datafile '/u01/prod/undotbs2.dbf'
size 10m
autoextend on;

alter tablespace undotbs1
add datafile '/u01/prod/undotbs2.dbf'
size 20m
autoextend on
next 1m
maxsize unlimited;

To confirm the addition of a new undo datafile:

select tablespace_name, file_name, bytes/1024/1024 "Mb"

from dba_data_files 

where tablespace_name in('UNDOTBS1');

We can add one more undo tablespace.  Even though we got multiple undo tablespaces in a database only one can be active at a given point of time.

Creating UNDO Tablespaces

create undo tablespace undotbs2 
datafile '/u01/prod/undotbs2.dbf'
size 20m
reuse
autoextend on;

select tablespace_name, contents, status from dba_tablespaces;

To check which undo is active now:

show parameter undo;

To make undotbs2 as the active: Switching Undo Tablespaces

alter system set undo_tablespace = UNDOTBS2;

alter system set undo_tablespace = undotbs2 scope = both;

show parameter undo;

This will confirm the undotbs2 is currently active.

When we have multiple undo tablespaces and to check which ones segments are active:

select segment_name, owner, tablespace_name, status 
from dba_rollback_segs;

Here all the segments related to undotbs1 are offline and the ones related to undotbs2 are online.  This means undotbs2 is active.

Views for UNDO tablespace

v$undostat
select * from v$undostat;

v$rollstat
select * from v$rollstat;

v$transaction
select * from v$transaction;

dba_undo_extents
select * from dba_undo_extents;