Creating the UNDO Tablespace

Creating the Undo Tablespace :

How to create undo tablespace :

->Every Oracle Database must have a method of maintaining information that is used to roll back,

or undo, changes to the database. Such information consists of records of the actions of

transactions, primarily before they are committed. These records are collectively referred

to as undo.

sys>>show parameter undo_tablespace;

sys>>create undo tablespace undotab datafile '/disk1/oradata/ORCL/undotab.dbf' size 10m;

SQL> show parameter undo;

NAME                                 TYPE          VALUE

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

undo_management                string           AUTO

undo_retention                       integer        2700

undo_tablespace                    string           UNDOTBS

Creating the undo tablespace :

SQL> create undo tablespace undotbs_1 datafile ''/disk1/oradata/ORCL/undotab1.dbf'' size 10m;

Tablespace created.

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

System altered.

SQL> show parameter undo;

NAME                                 TYPE           VALUE

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

undo_management                 string           AUTO

undo_retention                       integer          2700

undo_tablespace                      string          UNDOTBS_1

Set UNDO_RETENTION in the initialization parameter file.

UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 2400;

Dropping a tablespace:

SQL> drop tablespace undotbs including contents and datafiles;

Adding the undofile to undo tablespace:

sys>>alter tablespace undotbs add datafile '/disk1/oradata/ORCL/undo01.dbf' size autoextend on;

Increasing the size of the undofile:

sys>>alter database datafile '/disk1/oradata/ORCL/undo1.dbf' resize 10m;