0

We have a requirement to read data as of certain time (say T1) from, say, schema 1, fill some staging tables in schema 2 with that data. And then read data from some other tables of schema 1 as of T1 based on the current data in the schema 2 staging tables (i.e. schema 1 and schema 2 are joined in the subsequent queries).

Is there a way of achieving this without having to use AS OF in each of the (500+) queries in the stored procedure, wherever schema 1 tables appear? Also there are lots of commits in the stored procedure due to the large amount of being inserted into schema 2.

Business need is to have read consistency across all the queries on Schema 1 so it can be as of the start of the stored procedure execution. All the tables in the schema 1 are enabled for flashback queries. Issue we are facing is having to read the tables of schema 1 as of certain time, simultaneously with reading current data from tables in schema 2.

1

Yes you can use dbms_flashback.enable_at_time. This changes your current sessions perception of the whole database (all schema).

But keep in mind that you would have to write changes in a different session, so you cannot use insert from those statements.

There is a current video from Connor as of AskTom fame on this: https://youtu.be/lmrWmH1vMPc

Another option is, if you want to have a consistent but current view you can set your transaction isolation to read only and begin a new transaction to get read consistency across multiple queries.

If you want to mix both read and write then you would need to use ‚as of` for the read subquery. The problem you have with that (changing many statements) can be easily solved by actually generating those statements automated.

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