Use 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 19 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 112 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.
