Loading....

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..

Please follow and like us:

Last Update: June 29, 2009  

June 29, 2009 174 Arjun Raja
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?