Create new UNDO tablespace
                                                 Last update (2010-07-09 20:49:54)
                                                                                                                   Date added (2008-02-11 14:49:58)

Summary
If you want to create a new UNDO tablespace or move datafiles of UNDO tablespace to other filesystems the solution is to:
Create a new UNDO tablespace (put the datafiles where you want), set it as default UNDO tablespace, change init parameter and drop the OLD UNDO tablespace.

How to
Create a new UNDO tablespace
CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/filesystem01/undotbs_01.dbf' SIZE 300M AUTOEXTEND OFF;
Set it as the default undo tablespace of the database
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS;
Drop old undo tablespace. If uncommited transactions exists, leave it for later(after a restart maybe)
DROP TABLESPACE OLD_UNDOTBS INCLUDING CONTENTS AND DATAFILES;
Trying to drop the current UNDO tablespace will propably get you an:
ORA-30013: undo tablespace 'OLD_UNDOTBS' is currently in use
You have to restart the instance to be able to drop the tablespace. In case you don't want to restart the database you must find the processes which are using rollback segments of the current UNDO tablespace and kill them. To find the sessions use the following script:
(Rollback segment usage by sessions)


Change the init parameter at spfile and memmory before restart!
ALTER SYSTEM SET undo_tablespace=UNDOTBS1 SCOPE=BOTH;
Other links
Create new control file
Create new TEMPORARY tablespace
Move or rename datafiles
Move or rename System datafiles
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search