Post patch procedure for 10g bug 5387030

Posted on 2009-12-22
Last Modified: 2013-12-18
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,
Question by:kangiser
    LVL 3

    Expert Comment

    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.
    LVL 3

    Accepted Solution

    Drop command should be the following:
    SQL> drop tablespace UNDO_TBS including contents and datafiles;

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now