Tag: undo

There is no function in Oracle (up to 11g currently) that will allow you to reduce the size of the undo tablespace.

Sometimes you will need to free up space and the best way to get it will be to reduce the size of the undo tablespace.

The way to do this is basically to create a new undo tablespace, drop the old one and recreate it with less space.

Here are the exact steps:

1. First, create a script to recreate the undo tablespace exactly as it was (you will need to tweak it to use less space)

In sqlplus:

You can find the location of the undo tablespace datafile with:

select * from dba_data_files;

set long 20000 (to make sure that the output isnt truncated.)

Create the SQL to recreate the tablespace later:

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’, ‘UNDOTBS1’) from dual;

2. Create a second undo tablespace to use temporarily while you remove the first.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘[FILELOCATION/FILENAME’ SIZE 50 M AUTOEXTEND OFF;

3. You need to set the new Undo tablespace as default otherwise you wont be able to delete the first;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

4. Make sure that the default has been set correctly:

SHOW PARAMETER undo_tablespace;

5. Now drop the original tablespace:

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

6. Use you generated SQL to create the new undo tablespace. Make sure that you change the script to set the size correctly.

7. Next, you need to set the new tablespace as the default:

ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

8. Make sure that the new tablespace is now the default:

SHOW PARAMETER undo_tablespace;

9. Drop the second undo tablespace.

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;