7 Replies Latest reply: Nov 16, 2012 9:27 AM by j_DBA_sourav RSS

    Flushing UNDO TABLESPACE

    j_DBA_sourav
      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
          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
            Pavan Kumar
            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
              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
                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
                  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
                    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
                      solved

                      -Saha