AboutUse Oracle’s Flashback Version Query to not only see past data at a particular point in time but also to see how it changed over time.
I think everyone can attest to the fact that using Oracle’s Flashback Query functionality is advantageous to finding out what data looked like at a particular point in time. Oracle’s Flashback Query technology drastically changes the way DBAs research and recover from those mistakes, errors, foul-ups, blunders, we fat-fingered humans make and the applications we write.
It’s great to be able to select data from a table from a particular point in time but, being the investigative creatures we are, we often want to know how data was or has changed over time. To do this we can use Oracle’s Flashback Version Query feature. Oracle’s Flashback Version Query allows a user to query, retrieve, and view all changes made to rows for a time interval—displaying each version of a row as it has changed and providing a unique opportunity to see how that row has changed and pinpoint when changes occur. This can be vital when debugging inconsistencies in data within our databases.
Just as a quick refresher that will also serve as part of our total example, let’s first remember how the normal Flashback Query works (not Flashback Version Query).
1. Create a table
SQL> create table flash_test 2 (a number, b number, 3 constraint a_pk primary key (a)); Table created.
2. Insert some rows
SQL> INSERT INTO flash_test VALUES (1,1); 1 row created. SQL> INSERT INTO flash_test VALUES (2,1); 1 row created. SQL> INSERT INTO flash_test VALUES (3,1); 1 row created. SQL> COMMIT; Commit complete.
3. Verify the Data at some point in time
SQL> SELECT * FROM flash_test;
A B
---------- ----------
1 1
2 1
3 1
SQL> ho date
Wed Jun 17 17:51:31 MDT 2009
4. Insert some more rows and verify all rows can be SELECTed
SQL> INSERT INTO flash_test VALUES (4,2); 1 row created. SQL> INSERT INTO flash_test VALUES (5,2); 1 row created. SQL> INSERT INTO flash_test VALUES (6,2); 1 row created. SQL> COMMIT; Commit complete. SQL> ho date Wed Jun 17 18:11:29 MDT 2009 SQL> SELECT * FROM flash_test; A B ---------- ---------- 1 1 2 1 3 1 4 2 5 2 6 2 6 rows selected.
5. Use Flashback Query to SELECT the rows as they appeared before the second wave of INSERTs
SQL> SELECT * FROM flash_test
AS OF TIMESTAMP TO_TIMESTAMP ('06172009:17:52:00','MMDDYYYY:HH24:MI:SS');
A B
---------- ----------
1 1
2 1
3 1
Now suppose there are additional sessions that modify these same rows using INSERTs, UPDATEs, and DELETEs.
Someone DELETEs rows from the table.
SQL> DELETE FROM flash_test WHERE b=1; 3 rows deleted. SQL> COMMIT; Commit complete. SQL> ho date Wed Jun 17 18:14:10 MDT 2009
Someone re-INSERTs some of the rows.
SQL> INSERT INTO flash_test VALUES (1,1); 1 row created. SQL> INSERT INTO flash_test VALUES (2,1); 1 row created. SQL> COMMIT; Commit complete. SQL> ho date Wed Jun 17 18:15:08 MDT 2009
And someone UPDATEs some of the rows.
SQL> UPDATE flash_test SET b=9 WHERE a=1; 1 row updated. SQL> COMMIT; Commit complete. SQL> ho date Wed Jun 17 18:15:32 MDT 2009
It would be very normal for the initial session that INSERTed the rows to wonder what has happened to his/her data over time. To the rescue, Oracle Flashback Version Query can retrieve the various versions of specific rows and show how they have changed over a given time (TIMESTAMP) or SCN interval. The syntax is very simple and takes the form:
VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}
Such that we could query the previous data with the following SQL:
SQL> SELECT * FROM flash_test
2 VERSIONS BETWEEN TIMESTAMP
3 TO_TIMESTAMP ('06172009:17:52:00','MMDDYYYY:HH24:MI:SS')
4 AND TO_TIMESTAMP ('06172009:18:14:00','MMDDYYYY:HH24:MI:SS');
BUT WAIT! What really happens when I issue this seemingly simplistic SQL? I get the following error:
SQL> SELECT * FROM flash_test
2 VERSIONS BETWEEN TIMESTAMP
3 TO_TIMESTAMP ('06172009:17:52:00','MMDDYYYY:HH24:MI:SS')
4 AND TO_TIMESTAMP ('06172009:18:14:00','MMDDYYYY:HH24:MI:SS');
SELECT * FROM flash_test
*
ERROR at line 1:
ORA-30052: invalid lower limit snapshot expression
Now I have to say this really irks me as I’m testing this on a system that has no other activity and all my UNDO should be readily available. The problem here is with my undo_retention parameter that is set at the default of 900--r 15 minutes. The ORA-30052 is telling me that I’m trying to query something outside the undo_retention period and I’m trying to flashback to a version older then query time minus undo_retention time.
So, for grins, let’s try and reset our undo_retention parameter to 2.5 hours by:
ALTER SYSTEM SET undo_retention=9000;
We issue the query again and we get the same error!!!:
SQL> SELECT * FROM flash_test
2 VERSIONS BETWEEN TIMESTAMP
3 TO_TIMESTAMP ('06172009:17:52:00','MMDDYYYY:HH24:MI:SS')
4 AND TO_TIMESTAMP ('06172009:18:14:00','MMDDYYYY:HH24:MI:SS');
SELECT * FROM flash_test
*
ERROR at line 1:
ORA-30052: invalid lower limit snapshot expression
Have no fear. Let’s just play around with the TIMESTAMPS a bit. First, let’s go ahead and give Oracle what it really wants and specify a time period that falls within the old undo_retention period. Sure enough this works!!
SQL> SELECT * FROM flash_test
2 VERSIONS BETWEEN TIMESTAMP
3 TO_TIMESTAMP ('06172009:18:00:00','MMDDYYYY:HH24:MI:SS')
4 AND TO_TIMESTAMP ('06172009:18:15:00','MMDDYYYY:HH24:MI:SS');
A B
---------- ----------
3 1
2 1
1 1
6 2
5 2
4 2
1 1
2 1
3 1
9 rows selected.
Now, let’s retry the original SQL that gave us the error. Amazingly it now works!
SQL> SELECT * FROM flash_test
2 VERSIONS BETWEEN TIMESTAMP
3 TO_TIMESTAMP ('06172009:17:52:00','MMDDYYYY:HH24:MI:SS')
4 AND TO_TIMESTAMP ('06172009:18:14:00','MMDDYYYY:HH24:MI:SS');
A B
---------- ----------
6 2
5 2
4 2
1 1
2 1
3 1
6 rows selected.
What we really want to do, when researching these changes, is to issue the following SQL. It uses a few pseudocolumns specifically designed to investigate when changes where done (start and end SCN, start and end TIME), a transaction identifier, and the type of operation (U-UPDATE, I-INSERT, D-DELETE) performed.
SQL> SELECT versions_startscn as startscn,
2 versions_starttime as starttime,
3 versions_endscn as endscn,
4 versions_endtime as endtime,
5 versions_xid,
6 versions_operation,
7 flash_test.*
8 FROM flash_test
9 VERSIONS BETWEEN TIMESTAMP
10 TO_TIMESTAMP ('06172009:17:52:00','MMDDYYYY:HH24:MI:SS')
11 AND TO_TIMESTAMP ('06172009:18:16:00','MMDDYYYY:HH24:MI:SS');
STARTSCN STARTTIME ENDSCN ENDTIME VERSIONS_XID V A B
---------- ---------------------- ---------- ---------------------- ---------------- - -- --
2276312 17-JUN-09 06.15.24 PM 04000300AC040000 U 1 9
2276292 17-JUN-09 06.15.03 PM 08001900BA040000 I 2 1
2276292 17-JUN-09 06.15.03 PM 2276312 17-JUN-09 06.15.24 PM 08001900BA040000 I 1 1
2276193 17-JUN-09 06.14.06 PM 0600200017050000 D 3 1
2276193 17-JUN-09 06.14.06 PM 0600200017050000 D 2 1
2276193 17-JUN-09 06.14.06 PM 0600200017050000 D 1 1
2275992 17-JUN-09 06.11.21 PM 0500200016050000 I 6 2
2275992 17-JUN-09 06.11.21 PM 0500200016050000 I 5 2
2275992 17-JUN-09 06.11.21 PM 0500200016050000 I 4 2
2276193 17-JUN-09 06.14.06 PM 1 1
2276193 17-JUN-09 06.14.06 PM 2 1
2276193 17-JUN-09 06.14.06 PM 3 1
12 rows selected.
Flashback Query has its point in time beauty—being able to investigate the exact values for a given object. But when it comes to trying to figure out how and when changes to data occurred, Flashback Version Query can really help cut to the chase. Oh, the times I would have like to use this feature to catch fellow DBAs or developers trying to “fix” data.