Home > Oracle > recreating undo tablespace

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 steps

SQL> SELECT name,value FROM v$parameter WHERE name IN (‘undo_management’,’undo_tablespace’);

NAME VALUE
————————
undo_management AUTO
undo_tablespace UNDOTBS01

SQL>
SQL> CREATE UNDO TABLESPACE undotbs02
DATAFILE ‘/u01/app/oracle/oradata/nespets/undotbs02.dbf’ SIZE 1024M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;

SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS02′;
SQL> ALTER TABLESPACE undotbs01 OFFLINE;
SQL> DROP TABLESPACE undotbs01 INCLUDING CONTENTS AND DATAFILES;
SQL>

With these steps we created a new undo tablespace, set it as the system default undo tablespace and drop the old tablespace including the datafiles.

Advertisements
Categories: Oracle
  1. Imran
    July 12, 2012 at 11:46 am

    Very helpful.

    thanks.

    • mikey
      August 23, 2012 at 4:30 pm

      thanks for the comment imran

  2. March 29, 2016 at 9:37 am

    very easy to understand, thanks.

  1. No trackbacks yet.

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: