Recreate Undo Tablespace

–Identify undo tablespace name

SQL> SELECT NAME,VALUE
     FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME             VALUE
--------------- ----------
undo_management  AUTO
undo_tablespace  UNDOTBS1

–Define how undo tablespace was created

SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1')
 FROM dual;

CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
 ALTER DATABASE DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' RESIZE 28377088

–Create another, substitute undo tablespace

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
      'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 26214400
     AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M;

–Change parameter undo_tablespace value to newly created tablespace name. (I have started my DB by spfile)

SQL> ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE=spfile;

–Shutdown database

SQL> shutdown immediate;

–Start the database

SQL> startup;

–Take old undo tbs. into offline mode

SQL> ALTER TABLESPACE UNDOTBS1 OFFLINE;

–Drop undo tbs. including contents and datafiles

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: