This discussion is archived
7 Replies Latest reply: Nov 16, 2012 7:27 AM by j_DBA_sourav RSS

Flushing UNDO TABLESPACE

j_DBA_sourav Newbie
Currently Being Moderated
Hi all,

Our application team is running a query which deletes almost 50 million data at a time. So expectedly Undo tablespace is filled up completely and for oracle's basic property it is failing the query...

I don't want to increase the size of UNDO TBS.. I can't afford to do it..

Can I flush the Undo tablespace by any chance... ?

Or suggest me a way...

Thanks and Regards,
Saha
  • 1. Re: Flushing UNDO TABLESPACE
    asifkabirdba Guru
    Currently Being Moderated
    You don't need to flush any thing from the undo tablespace. It will reuse the undo data automatically.

    You can create a new undo tablespace and make it active for your database. Later on drop your old undo tablespace.


    Regards
    Asif kabir
  • 2. Re: Flushing UNDO TABLESPACE
    PavanKumar Guru
    Currently Being Moderated
    Hi,
    Our application team is running a query which deletes almost 50 million data at a time
    Does it carried out at business hours. ?
    Why can't you change your logic to carry out in batch wise with few commits, that would help you out - unnecessarily don't hammer the undo.
    As you are sure that your application is intended to carry out change.

    - Pavan Kumar N
  • 3. Re: Flushing UNDO TABLESPACE
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi Saha;

    Please see below links:
    http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=18486
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6894817116500

    Regard
    Helios
  • 4. Re: Flushing UNDO TABLESPACE
    Karan Kukreja Journeyer
    Currently Being Moderated
    Hi ,

    Try to execute the query in off business hours.. and also, try to run the application with some commits in between. This might help.

    Other option is to create another Undo tablespace and work on it. The links that have been provided in the above post seem to be helpful too.


    Rgrds
    Kk
  • 5. Re: Flushing UNDO TABLESPACE
    j_DBA_sourav Newbie
    Currently Being Moderated
    Hi Asif,

    My Undo retention is 1 hour..
    My undo tablespace is of 2.4GB
    We can not afford to increase the size of undo tablespace .. If we add a new undo tablespace what will be your action point for that case.. I don't understand the benefit of creating a new one.

    Coming back to the last point .. "IT is flush undo data automatically"

    If with in UNDO_RETENTION time undo tablespace is filled and my query fails, then if another query is going to do a DML and tries to use UNDO segments then, I think that will also fail because of unavailibility of UNDO SEGMENTS..
    Now if after undo_retention time period if again someone asks for another DML which will have to use UNDO segments will the undo tablespace be releasing segments as per the requirements or it will relase all the old and failed undo segments.... ( I think the fist is right.. do correct me if I'm wrong)..

    Please suggest...

    Many thanks to all for your great help..
    -Saha
  • 6. Re: Flushing UNDO TABLESPACE
    asifkabirdba Guru
    Currently Being Moderated
    http://www.morganslibrary.com/reference/tablespaces.html#tsud

    Go to UNDO TABLESPACE section of that link.



    Yes, as your UNDO_RETENTION time is 1 hour it will be flushed after 1 hour. You can set the UNDO_RETENTION to 20 minutes for few hours and then again bring it backup to 1 hour.


    Regards
    Asif Kabir
  • 7. Re: Flushing UNDO TABLESPACE
    j_DBA_sourav Newbie
    Currently Being Moderated
    solved

    -Saha

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points