Wednesday, November 26, 2008

How to reduce UNDO tablespace

Undo tablespace is used to keep unrolled data. Sometime it may increase tremendously because some of the SQL statement is not committed.

We can reduce the tablespace sizing for undo tablespace by following method. Assuming your current undo tablesapce name is 'UNDOTBS':
1. Ensure there' s no application is connected to the database.
2. Login as Oracle user and connect to SQL as sysdba. Then create another undo tablespace and set as default.
SQL>create undo tablespace undonew datafile '/undonew.dbf' size 1024M;
SQL>alter system set undo_tablespace='undonew';
By doing this, it will change the default undo_tablespace id undonew in spfile. If you are not using spfile. then you may skip this and edit the undo_tablespace parameter in initfile.
3. Drop the old undo tablespace including all.
SQL>drop undotbs tablespace including contents and datafiles;

4. Shutdown database.
5. After database has been shutting down, you may edit your initfile to change the undo_tablespace parameter to 'undonew'.
6. Startup database
7. Create the old database 'UNDOTBS' and set it as default.
SQL>create undo tablespace UNDOTBS datafile '/undo01.dbf' size 1024M;
SQL>alter system set undo_tablespace='UNDOTBS';

As usual, if you are using initcfile, change the parameter value after shutting down database in initfile.

8. Drop the undonew tablespace including contents.
SQL>drop undonew tablespace including contents and datafiles;

9. Shutdown database.
10.Change the undo_tablespace parameter from undonew to undotbs if you are using initfile.
11. startup database.

0 comments: