Oracle flashback query syntax - all tables to same timestamp
3
votes
2
answers
6965
views
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.
Asked by SqlRyan
(1206 rep)
Nov 1, 2016, 05:16 PM
Last activity: Nov 1, 2016, 05:38 PM
Last activity: Nov 1, 2016, 05:38 PM