Oracle DBA Tips and Techniques

By Arjun Raja

Flashback query

Posted by Gavin Soorma on June 29, 2009

FLASHBACK QUERY

FLASHBACK QUERY feature allows you to perform queries on the database as of a certain time or user-specified SCN.

FLASHBACK VERSIONS QUERY feature allows you to use the VERSIONS clause to retrieve all of the versions of the rows that exist between two points in time, or two SCN’s.

YOU REQUIRE THE SELECT ANY TRANSACTION PRIVILEGE TO BE ABLE TO ISSUE A QUERY AGAINST FLASHBACK_TRANSACTION_QUERY.

SQL> create table test(salary number(10));

Table created.

SQL> insert into test values(1000);

1 row created.

SQL> select * from test;

SALARY
———-
1000

SQL> update test set salary=2000;

1 row updated.

SQL> commit;

Commit complete.

SQL> update test set salary=4000;

1 row updated.

SQL> commit;

Commit complete.

SQL> update test set salary=5000;

1 row updated.

SQL> commit;

Commit complete.

SQL> select salary from test versions between scn minvalue and maxvalue;

SALARY
———-
5000
4000
2000

Using FLASHBACK VERSIONS query

select salary from test versions between timestamp
TO_TIMESTAMP( ‘2005-09-12 14:38:00′,’YYYY-MM-DD HH24:MI:SS’) and
TO_TIMESTAMP( ‘2005-09-12 14:43:00′,’YYYY-MM-DD HH24:MI:SS’);

SALARY
———-
5000
4000
2000

Select to_char(versions_starttime,’DD-MON HH:MI’) “START DATE”,
to_char (versions_endtime,’DD-MON HH:MI’) “END DATE”,VERSIONS_XID,VERSIONS_OPERATION,empno FROM VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

START DATE END DATE VERSIONS_XID V SALARY
———— ———— —————- – ———-
12-SEP 02:40 0700190004010000 U 5000
12-SEP 02:40 12-SEP 02:40 0A00290001020000 U 4000
12-SEP 02:40 12-SEP 02:40 07000C0005010000 I 2000

Using FLASHBACK TRANSACTION query:

The flashback transaction query helps to get the actual query run:

SQL> Select table_name,operation, undo_sql from flashback_transaction_query Where XID=’06002C00F7060000′;

TABLE_NAME
——————————————————————————–
OPERATION
——————————–
UNDO_SQL
——————————————————————————–
TEST
UPDATE
update “ARJUN”.”TEST” set “SALARY” = ‘1000’ where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

TEST
INSERT
delete from “ARJUN”.”TEST” where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

TABLE_NAME
——————————————————————————–
OPERATION
——————————–
UNDO_SQL
——————————————————————————–

To narrow down to a point-in-time…

Select table_name,operation ,undo_sql
From flashback_transaction_query where start_timestamp >= to_timestamp ( ‘2005-09-12 14:38:00′,’YYYY-MM-DD HH24:MI:SS’)
AND TABLE_OWNER=’ARJUN’;

TABLE_NAME
——————————————————————————–
OPERATION
——————————–
UNDO_SQL
——————————————————————————–
TEST
UPDATE
update “ARJUN”.”TEST” set “SALARY” = ‘4000’ where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

TEST
UPDATE
update “ARJUN”.”TEST” set “SALARY” = ‘2000’ where ROWID = ‘AAAC7iAAFAAAAAPAAA’;

Please remember that the FLASHBACK VERSIONS QUERY cannot be used to query:

1. EXTERNAL TABLES
2. TEMPORARY TABLES
3. FIXED TABLES
4. VIEWS

IT also cannot span DDL commands, ie…alter table etc..

Advertisements

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: