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.