Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

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,
  • 2
1 Solution
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.
Drop command should be the following:
SQL> drop tablespace UNDO_TBS including contents and datafiles;

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now