Create UNDO tablespace in Oracle

In Oracle 8i and below, Rollback Segments provide read consistency and the ability to rollback transactions. In Oracle 9i, Undo segments can be used to provide this functionality. The advantage of using Automatic Undo Management is that it relieves the DBA of manually creating, sizing and monitoring the rollback segments in the database.

Drop Existing Rollback segments and create new UNDO tablespace

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
ROLL301 RBS3



SQL> select file_name,tablespace_name, bytes from dba_data_files where tablespace_name = 'RBS3';


SQL> select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;

SEGMENT_NAME
============

alter rollback segment ROLL301 offline;
alter rollback segment ROLL302 offline;
alter rollback segment ROLL303 offline;

SQL> alter rollback segment ROLL301 offline;

Rollback segment altered.

SQL> alter rollback segment ROLL302 offline;

Rollback segment altered.


==============================

SQL> drop rollback segment ;

SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM from dba_rollback_segs;


SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM dba_rollback_segs;

'DROPROLLBACKSEGMENT'SEGMENT_NAME';'
------------------------------------------------------

drop rollback segment ROLL301;
drop rollback segment ROLL302;

alter tablespace RBS3 offline;


SQL> alter tablespace RBS3 offline;
SQL> alter tablespace RBS2 offline;

drop tablespace RBS2;

Tablespace altered.

Enabling Automatic Undo Management

Since the default undo management mode is MANUAL, the instance must be told to use AUTO mode at instance startup. To do this the following initialization parameters can be set:

UNDO_MANAGEMENT = AUTO # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION = 900 # The time undo is retained.
# Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin
# SQL statements are issued.

Create Undo Tablespace

SQL> create undo tablespace APPS_UNDOTS1 datafile '/db2/oradata/dev/data/undodbs01.dbf' size 3000M reuse extent management local;

Add Datafile

SQL> ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf' size 3000M;

Add a datafile

ALTER TABLESPACE undotbs_01
ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Resize an undo datafile

SQL> ALTER DATABASE DATAFILE '/db2/oradata/dev/data/undodbs04.dbf' RESIZE 40000M;

SQL> create undo tablespace APPS_UNDOTS2 datafile '/db2/oradata/dev/data/undodbs04.dbf' size 3000M reuse extent management local;

SQL> ALTER TABLESPACE APPS_UNDOTS2 ADD DATAFILE '/db2/oradata/dev/data/undodbs05.dbf' size 2000M;


Dynamic Parameters.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
SQL> ALTER SYSTEM SET UNDO_RETENTION=1800;
SQL> ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

Static Parameters.

SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Drop an undo tablespace.
DROP TABLESPACE undotbs_01;

Monitoring

Undo information can be queried using the following views:

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1