Recreating UNDO Tablespace

If your undo tablespace has grown to a large size and you need to recreate it , you can do so with the following easy steps:

STEP 1: Query V$PARAMETER to see the default UNDO tablespace currently active.

SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
_______________________
undo_management AUTO
undo_tablespace UNDOTBS2


STEP 2: Create a new tablespace that will be the new default tablespace.

CREATE UNDO TABLESPACE undotbs02
DATAFILE '/oradata/oracle/oradata/DB1/undotbs02.dbf' SIZE 500M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;

STEP 3: Set this newly created UNDO tablespace to default UNDO tablespace.

ALTER SYSTEM SET undo_tablespace = 'UNDOTBS02';

STEP 4: Set the old tablespace to OFFLINE to be eligible to drop.

ALTER TABLESPACE undotbs2 OFFLINE;

STEP 5: Drop the old UNDO tablespace.

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;

-----------------------------------------------------------------------------------------
tHANKS fOR rEADING tHIS aRTICLE.