Undo Management

    To check whether the system is using AUTO or MANUAL undo management

SQL>show parameter undo_management

     To check the undo tablespace for the database

SQL>show parameter undo_tablespace

3     To check the retention period of  undo tablespace

SQL>show parameter undo_retention

4     To create Undo tablespace

            In create database script add undo tablespace clause as

Create database testdb

-          -  - - - - -

Undo tablespace undo1 datafile ‘/u01/app/oradata/test/undo01.dbf’ size 20m autoextend on;

             Using create undo tablespace command:

SQL>create undo tablespace undo1 datafile ‘/u01/app/oradata/test/undo01.dbf’ size 20m;

5    To increase the size of undo tablespace

Add additional datafile to the existing undo tablespace

SQL>alter tablespace undo1 add datafile ‘/u01/app/oradata/test/undo02.dbf’ size 20m autoextend on;

6    To switch undo tablespace

The following will switch the default undo tablespace to undo1

SQL>alter system set undo_tablespace=undo1;

7    To drop Undo tablespace

We can’t drop the default undo tablespace or the tablespace that is currently used by database.

The following drops the tablespace named undotbs1

SQL>drop tablespace undotbs1;