|
FLASHBACK in Oracle 10g database (FLASHBACK QUERY)
This article explain FLASHBACK QUERY capabilities of the oracle 10g database.
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
2 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 chu 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='07000C0005010000';
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..
|
