Oracle flashback query syntax - all tables to same timestamp

Question

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.

Answer 1

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

Answer 2

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;

• SqlRyan: 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.
• Balazs Papp: @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/…
• SqlRyan: @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 :)