3

I'm not finding a lot of examples online of how to use Oracle Flashback Query and I'm hoping somebody can clarify syntax. I'm trying to execute a query with a large number of tables in it, but the syntax seems to require me to specify a timestamp for every single table involved in the query.

For example, the following query gets me consistent data, but it requires me to specify a timestamp for each table:

select t1.Field1, t2.Field2
  from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1
  join table2 as of timestamp to_timestamp(2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t2
    on t1.somekey = t2.somekey

The following example (which is how most examples online look) gives past data for table1, but joined to current data from table2 - they don't line up:

select t1.Field1, t2.Field2
  from table1 as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss') t1
  join table2 t2
    on t1.somekey = t2.somekey

Since I have a large number of tables, I'm hoping for a way I can execute the entire query against a flashback timestamp - something like this:

select (as of timestamp to_timestamp('2016-11-01 02:00:00','yyyy-mm-dd hh24:mi:ss'))
       t1.Field1, t2.Field2
  from table1 t1
  join table2 t2
    on t1.somekey = t2.somekey

Anybody know if this is possible, or if the current syntax doesn't allow it? I'm using 11.2 if it matters, but nothing I see online suggests that this has changed in 12.

5

You can have not only a single query, but your whole session execute queries against a specified time using flashback query. You can set this time with DBMS_FLASHBACK.ENABLE_AT_TIME. Sample data:

create table t1 (id number);
create table t2 (id number);
insert into t1 values (1);
insert into t2 values (1);
commit;

Current time and data:

SQL> select systimestamp from dual;

SYSTIMESTAMP
-----------------------------------
01-NOV-16 06.29.21.571559 PM +01:00

SQL> select id from t1 natural join t2;

        ID
----------
         1

Now modify data:

update t1 set id = 2;
update t2 set id = 2;
commit;

SQL> select id from t1 natural join t2;

        ID
----------
         2

Now run the query with a specified time:

SQL> exec dbms_flashback.enable_at_time(timestamp'2016-11-01 18:29:22');

PL/SQL procedure successfully completed.

SQL> select id from t1 natural join t2;

        ID
----------
         1
5

Look at the DBMS_FLASHBACK package and the ENABLE_AT_TIME procedure (12.1 Documentation). This allows you to do something like the following:

execute DBMS_FLASHBACK.Enable_At_Time(TIMESTAMP '2016-11-01 02:00:00');

The session will then run all queries as of the SCN most closely matching the provided timestamp.

SELECT t1.Field1, t2.Field2
FROM table1 t1
JOIN table2 t2 ON t1.somekey = t2.somekey

To stop doing flashback queries as of the set timestamp, simply disable it.

execute DBMS_FLASHBACK.Disable;
  • This looks to be exactly what I want to do - why wouldn't Oracle even make mention of this syntax when they're explaining how flashback query works? This seemed like a glaring hole in the functionality - I'm glad to hear that it's something supported. – SqlRyan Nov 1 '16 at 17:45
  • 1
    @SqlRyan Explain where? This is mentioned in the documentation. Database Development Guide - Using Oracle Flashback Technology - Using DBMS_FLASHBACK Package: docs.oracle.com/database/121/ADFNS/… – Balazs Papp Nov 1 '16 at 19:52
  • @BalazsPapp Touche - reading it now, it seems obvious, but maybe I just didn't make it that far in the documentation. Shame on me for only reading halfway down :) – SqlRyan Nov 1 '16 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.