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

2 comments:

Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.