This content has been marked as final. Show 8 replies
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]
user10313295 wrote:Why don't you simply try? The links have been provided how to use the Flashback Query or Flashback Table features.
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?
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):
Let me explain :)
block(1) user(A) at 10:00am
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).
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).
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
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.
select * from tab as of timestamp systimestamp - interval '120' minute;