vicker313 tech blog

December 21, 2009

Reduce Size of UNDO Tablespace

Filed under: Oracle — Tags: , — vicker313 @ 8:00 am

UNDO tablespace can grow quickly if long and uncommitted transactions are executed, sometime even take out the whole hard disk and crash the database, and lastly database itself not even can be started up.

To reduce the size of the data file (especially when it has taken all your hard disk space), we need to recreate the default UNDO tablespace because the resize data file statement will not work:
alter database datafile 'undo.bdf' resize 100M;

  1. Log in as sysdba
    connect / as sysdba
  2. Start database in nomount state
    startup nomount;
  3. Create temporary UNDO tablespace in anywhere got free space
    create undo tablespace undo2 datafile '/tmp/undo2.dbf' size 50M autoextend off;
  4. Set the temporary UNDO tablespace as default UNDO tablespace
    alter system set undo_tablespace = undo2;
  5. Drop the original UNDO tablespace.
    drop tablespace undo1 including contents and datafiles;
  6. Recreate the original UNDO tablespace.
    create undo tablespace undo1 datafile '/u01/database/undo1.dbf' size 500M autoextend on next 5M maxsize 1000M;
  7. Set back the original UNDO tablespace to default UNDO tablespace
    alter system set undo_tablespace = undo1;
  8. Drop the temporary UNDO tablespace
    drop tablespace undo2 including contents and datafiles;
Advertisements

2 Comments »

  1. […] Size of TEMP Tablespace Similar to Reduce Size of UNDO Tablespace, you need to recreate temporary tablespace in order to reduce the size. Issue a resize statement […]

    Pingback by Reduce Size of TEMP Tablespace « vicker313 tech blog — December 28, 2009 @ 8:07 am

  2. This just got me out of a a very tight space issue. Thanks! Really really useful.

    Comment by hossthelegend — September 26, 2012 @ 6:58 pm


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: