Transaction in the undo tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Transaction in the undo tablespace

  1. #1
    Join Date
    Jul 2005
    Posts
    31

    Transaction in the undo tablespace

    The application is incredibly slow and is not allowing the users to commit any transactions The reason to this is that my undo tablespace has been at 100% for a couple of hours now. Earlier today one of the developers had run a huge delete statement which might have caused this scenario. I had to kill his process cause the archive log destination filled up.
    What query could I run to see which are the transactions in the undo tablespace and also what could I do to get the that cleared out faster without adding more space to the undo tablespace?
    Would a bounce to the database solve this issue (this would be my last alternative).

    Your input would be greatly appreciated.

    DBA01

  2. #2
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    the transactions are too slow rolling back affected by hanged processes...
    yes aborting can help...since after u start it up rolling back gets
    faster
    Behind The Success And Failure Of A Man Is A Woman

  3. #3
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Hi,

    But Be Careful, shutdown immediate also might hang up ...

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    not immediate....abort...or refresh the entire server...to release all
    hanged up process
    Behind The Success And Failure Of A Man Is A Woman

  5. #5
    Join Date
    Jul 2005
    Posts
    31
    Thanks for all your responses.

    But what helped me bring the percentage on the undo tablespace down was that I altered the system to set the undo_retention parameter to 0. This way the undo tablespace no longer held on to any of the transactions and the SMON was able to clean it out. But it was not immediate it did take a couple of hours to do so.

    Thanks once again,
    DBA01

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    There will be an entry in v$transaction for all active transactions (even ones that are rolling back). Look at the column USED_UBLK (assuming your database version is either 9x or 10x) to see how many undo blocks they are using.

    Honestly, you should look to have enough space available for you to create a larger undo tablespace if need be. You shouldn't leave yourself in a position where you cannot expand any tablespace if the need arises.
    Assistance is Futile...

  7. #7
    Join Date
    Sep 2006
    Posts
    2
    Hi,
    UNDO tablespace 100% is normal. Can you run statspack to find out where is the bottleneck is ?

    Regards,
    Satheesh Babu.S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width