Oracle: Data consistency across multiple tables to be displayed

user_17634649 0
Viewed 43

I have 3 reports based on 3 different tables, which ideally should match each other in audit. They are updated sequentially once in a day.

The problem here is when one of the table is updated and second one is in progress, the customer sees data discrepancy between the reports for some time.

We tried the solution where in we commit after all 3 tables are updated but we started having issue on undo tbsp. The application have many other things running on.

I am looking for a solution where in we can restrict the user to show data to a specific point, and he must see updated data only after all 3 tables are refreshed/updated.

plsql oracle sql
2 Answers

I think you can use select * for update for all 3 tables befor start updating procedure.
In that case users can select data and will see only not changed data till update session will not finish and make commit.

user_60032040

You can use a flashback query to show data as-of a point in time:

select * from table1 as of timestamp timestamp '2021-12-10 12:00:00';

The application would need to determine the latest time when the tables were synchronized - perhaps with a log table that records when the update process last started. However, the flashback query also uses the UNDO tablespace. But the query should at least use less UNDO since some of the committed transactions will now free up some space.

user_4091720
Related