Creating an UNDO Tablespace

Whenever a transaction is initiated, it can be rolled back to bring it back to its original state. This original state of data is stored in rollback (UNDO) segments which are created in a tablespace called UNDO or Rollback Tablespace. The data written in these segments are called Undo Records. These Undo records are very critical for the database to function properly and are used to:

1. Rollback the transaction to its original state whenever ROLLBACK is issued or the transaction fails abruptly due to loss of database connectivity or instance crash.
    
2. Recover the database.
    
3. Provide Read Consistent Image of the data, so that read do not block reads.
    
4. Recover an Object to an earlier point in time using Flashback Query.

To use the automated UNDO management features, you must first create an UNDO tablespace. The UNDO tablespace can be created by using the UNDO clause of the CREATE TABLESPACE command or by using the CREATE DATABASE command. The sections below will look at each of these methods in greater detail.

Creating an UNDO Tablespace Using the CREATE TABLESPACE Command

The example below illustrates how to use the UNDO clause of the CREATE TABLESPACE command to create an UNDO tablespace:

CREATE UNDO TABLESPACE undo_tbs
DATAFILE '/ora100/oracle/mydb/data/mydb_undo_tbs_01.dbf' SIZE 100m
AUTOEXTEND ON;

The UNDO tablespace created by this example is called UNDO_TBS. The CREATE UNDO TABLESPACE command syntax is very similar to the CREATE TABLESPACE command, including the datafile and size clauses. Only the datafile clause and a restricted form of the extent managment clause of the CREATE TABLESPACE command can be used when creating an UNDO tablespace. This means that no default storage characteristics can be defined. It is also worthwhile to note that Oracle creates the UNDO tablespace as a locally managed tablespace; there is no option to create it as a dictionary-managed tablespace.

After an UNDO tablespace has been created, it, along with the undo segments within it, will be brought on line each time the database is started. Messages that appear in the alert log each time the database is started will show this.

Note: Even if you create an UNDO tablespace, you will still need to keep the SYSTEM rollback segment.

Creating an UNDO Tablespace Using the CREATE DATABASE Command

The CREATE DATABASE command has been modified to support the definintion of UNDO tablespaces during the database creation process. This is done using the undo tablespace clause as shown in the example below:

CREATE DATABASE mydb
CONTROLFILE REUSE
LOGFILE GROUP 1 ('d:\oradata\mydb\redo\mydb_redo_01a.log',
                 ' e:\oradata\mydb\redo\mydb_redo_01b.log ')  SIZE 50K,
        GROUP 2 ('d:\oradata\mydb\redo\mydb_redo_02a.log',
                 ' e:\oradata\mydb\redo\mydb_redo_02b.log ')  SIZE 50K
MAXINSTANCES 1 MAXLOGFILES 5  MAXLOGHISTORY 100 MAXDATAFILES 100
ARCHIVELOG
DATAFILE  'e:\oradata\mydb\mydb_system_01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED,
DEFAULT TEMPORARY TABLESPACE temp_ts
TEMPFILE  'e:\oradata\mydb\mydb_temp_ts_01.dbf' SIZE 20m
UNDO TABLESPACE undo_ts DATAFILE  'e:\oradata\mydb\mydb_undo_ts_01.dbf'
SIZE 50M AUTOEXTEND OFF;

The UNDO tablespace created in this example is called undo_ts. By using the DATAFILE clause, we were able to define the name and location of the datafile associated with this UNDO tablespace. Note that the SIZE and AUTOEXTEND clauses.have also been included.

When using the CREATE DATABASE command, there are a couple of rules to consider relating to UNDO tablespaces. These rules differ depending on how the database is configured.

1. If the database instance is not configured for automated UNDO management, and the UNDO TABLESPACE clause is ommitted, the CREATE DATABASE statement will work as it always has -- no UNDO tablespace will be created.
    
2. If the instance is configured for automated UNDO management, the default behavior of the CREATE DATABASE statement changes. If you do not include the undo tablespace clause, Oracle creates an UNDO tablespace by default. The tablespace will be called SYS_UNDOTBS. The tablespace will be created with a default size of 100M for the database datafile.

Dropping an UNDO Tablespace

You can use the DROP TABLESPACE command to drop an UNDO tablespace. If the UNDO tablespace happens to be the active UNDO tablespace, Oracle will generate an error at this command.