8 Replies Latest reply: Jun 17, 2009 7:41 AM by Uwehesse-Oracle RSS


      I am using 10g.
      My parameter settings are as follows:


      undo_management string AUTO
      undo_retention integer 900
      undo_tablespace string UNDOTBS1

      Is undo_retention =900 means data can be restored within 15 minits of its deletion from the table?
        • 1. Re: undo_retention
          Oded Raz
          No,Its specifies the time period in seconds for which a system retains undo data for committed transactions.

          The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.
          • 2. Re: undo_retention
            I guess you are about to configure flash back in your db s it is you are right.
            • 3. Re: undo_retention
              UNDO_RETENTION parameter specifies the length of time that undo data for committed transactions will be kept available to provide read consistency.

              [UNDO behavior in Oracle 9i and 10g under microscope|http://www.orafaq.com/node/61]

              • 4. Re: undo_retention
                I didnot get clear idea.
                My question is:
                Undo_retention parameter is set to 900. means 15 minutes.
                Yestreday i deleted some data and committed. whether I am able to restore data today?
                • 5. Re: undo_retention

                  You can use oracle flashback features,


                  • 6. Re: undo_retention
                    Randolf Geist
                    user10313295 wrote:
                    I didnot get clear idea.
                    My question is:
                    Undo_retention parameter is set to 900. means 15 minutes.
                    Yestreday i deleted some data and committed. whether I am able to restore data today?
                    Why don't you simply try? The links have been provided how to use the Flashback Query or Flashback Table features.

                    Regarding the UNDO_RETENTION setting: You need to understand that 10g introduced automatic undo retention tuning, so your setting might not be relevant depending on some other constraints.

                    See the following paragraph from the official documentation for an explanation of the automatic undo retention tuning: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/undo002.htm#sthref1378

                    You can see the actual retention period by checking the TUNED_UNDORETENTION column of the dynamic performance view V$UNDOSTAT.


                    Oracle related stuff blog:

                    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                    • 7. Re: undo_retention

                      Let me explain :)

                      block(1) user(A) at 10:00am
                      block(2) user(A)
                      block(3) user(A) at 10:15am block(3) was changed by user (B). now block(3) user(B) and B commits block(3) now changed block goes to undo ie block(3) user(A).
                      block(4) user(A)

                      when user(C) queries the block(3), C can see the value (B).
                      But when the user (A) quries block(3), (A) should see the value(A) not the changed value by B. (A) sees the undo block till the undo_retention time.

                      This maintains the consistency of records for (A).

                      • 8. Re: undo_retention
                        First of all: The main reason for the parameter UNDO_RETENTION is to avoid ORA-1555 Snapshot too old errors. An not committed transaction will always keep the for read constistency (and maybe rollback) neccessary before images in the undo tablespace. If the transaction, that caused the before images commits, than they are kept as long as UNDO_RETENTION specifies, which can be autotuned since 10g in order to let long running queries proceed without ORA-1555.

                        As an additional benefit, you may use before images to do a flashback query (9i) or flashback table (>=10g), like
                        select * from tab as of timestamp systimestamp - interval '120' minute;
                        Shows the state of the table before 2 hours, recontructed from before images. Quick answer to your question: Maybe. If the before images needed for it are not yet overwritten, that is.