Managing Undo Tablespaces

Creating an Undo Tablespace

Using CREATE DATABASE to Create an Undo Tablespace

CREATE DATABASE rbdb1

     CONTROLFILE REUSE

     .

     .

     .

     UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oracle/rbdb1/undo0101.dbf';

If the undo tablespace cannot be created successfully during CREATE DATABASE, the entire CREATE DATABASE operation fails. You must clean up the database files, correct the error and retry the CREATE DATABASE operation.

Using the CREATE UNDO TABLESPACE Statement

CREATE UNDO TABLESPACE undotbs_02 DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;

Note:

You can create more than one undo tablespace, but only one of them can be active at any one time.

Altering an Undo Tablespace

ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u01/oracle/rbdb1/undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace

DROP TABLESPACE undotbs_01;

Note:

An undo tablespace can only be dropped if it is not currently used by any instance.

Switching Undo Tablespaces

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

Un Assign the current Undo Tablespace

ALTER SYSTEM SET UNDO_TABLESPACE = '';

Total space of Undo Tablespace
select tablespace_name,sum(bytes)/1024/1024/1024 "GB",sum(MAXBYTES)/1024/1024/1024 from dba_data_files where tablespace_name like '%UNDO%' group by tablespace_name;

Used space of Undo Tablespace
select tablespace_name,sum(bytes)/1024/1024/1024 "GB" from dba_segments where tablespace_name like '%UNDO%' group by tablespace_name;

The following example shows the results of a query on the V$UNDOSTAT view.

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,

UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"

FROM v$UNDOSTAT WHERE rownum <= 144;