jump to navigation

Flashback Version March 9, 2007

Posted by Muhammad Habib in DBA.
trackback

As of oracle database 10g, you can display the different versions of rows that existed during specified intervals.

Flashback version queries require that the DBA has set a nonzero value for the UNDO_RETENTION initialization parameter. If the UNDO_RETENTION value is too low, you may get an ORA- 30052 error.

To prepare for these examples, delete the old rows from MY_TABLE:

delete from MY_TABLE;
Next, repopulate MY_TABLE:
select SysTimeStamp from DUAL;
insert into MY_TABLE
select * from MY_TABLE_OLD;

select SysTimeStamp from Dual;

Then, wait for a few minutes and update all rows:

select sysTimestamp from dual;

update MY_TABLE set categoryName = ‘ABCD’;

SELECT SYSTIMESTAMP FROM DUAL;

To execute a flashback version query, use the version between clause of the select command. You can specify either the timestamp or the SCN, In this example, the format for the timestamp clause is based on Oracle’s standard format(select systimestamp from dual for the current value):

select * from MY_TABLE
versions between timestamp
to_timestamp(’03-MAR-07 17.10.10′, ‘DD-MON-YY HH24.MI.SS’)
AND
to_timestamp(’03-MAR-07 17.25.10′, ‘DD-MON-YY HH24.MI.SS’);

When you execute the query, oracle will return one row for
each version of each row that occured between the start and
end points you specify in the versions between clause. For
the rows that are returned, you can query additional pseudo-columns.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: