Post patch procedure for 10g bug 5387030

We use a hosted Oracle application and I know nothing about Oracle or any other database. We had a giant leap in disk usage and our hosting company couldnt determine the problem so I did some research myself and figured out we had the Oracle 10g bug 5387030 (Automatic tuning of undo_retention causes unusual extra space allocation). The hosting company verified we had the bug and we are currently having them implementing the fix.

They have already installed the patch on 2 out of 3 servers and I havent seen any drop in disk usage, how do we go about regaining the wasted space caused by this bug?

I read that you should bounce the server after applying the patch to regain the space, but since its like pulling teeth to get our hosting company to do anything, I wanted to verify this was the next step.

Thanks in advance,
Marty
kangiserAsked:
Who is Participating?
 
logictankCommented:
Addition:
Drop command should be the following:
SQL> drop tablespace UNDO_TBS including contents and datafiles;
0
 
logictankCommented:
This is one method and is the most straight forward:
- Create a new undo tablespace with a smaller size.
SQL> create undo tablespace UNDO_TBS1 datafile 'undotbs01.dbf' size 1000M;

- Point to the new UNDO tablespace;
SQL> alter system set undo_tablespace=UNDO_TBS1;

-- Drop the old tablespace.
SQL> drop tablespace UNDO_TBS including contents;
Note: For the 'drop' command this will be the name of your existing UNDO Tablespace.  You may need to get the database to a "Quiet" state before dropping the original tablespace (Stop applications and user connections) or you will receive:
ORA-30013 : undo tablespace '%s' is currently in use

You also could try to shrink the datafile(s) in the existing UNDO Tablespace but it doesn't always work:
alter database datafile .. resize;
You will usually receive 'ORA 3297: file contains <num> blocks of data beyond requested RESIZE value' if there are active UNDO Segments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.