Help : Getting consistent view

Hi! All Oracle users,

    Currently, my project is running a Oracle ver 7. database in HP-UX environment. My colleagues have a question on how to get a consistent image for some tables in the database for generating reports. The main requirement is consistency when retrieving data, as some related tables are not stable and subject to update by other processes. Actually, only part of not the whole table is retrieved governed by the WHERE clause. We think of several alternatives to solve it, but all have drawback and they are summarized as follow :

1. Acquire EXCLUSIVE lock on those required tables before doing select. The drawback is no update can be done by other processes on those rows not involved in the reporting.
2. Use SET TRANSACTION READ ONLY statement The drawback is that it needs a huge rollback segment and no update statement can be made after the SET TRANSACTION point.
3. Use SELECT FOR UPDATE statement to acquire neccessary ROW LOCK. The drawback is that it only apply on single table and it needs to issue this statement one by one for all needed tables. There incurs time gap in getting table snapshot.
4. Use Oracle 7 replication facility to create an exact table image upto the time when reporting starts and resumes after finished report generation. The drawback is that it requires extra space and effort to keep the reporting database.

    Does any one have idea about what Oracle facilities or 3rd party products can solve the above mentioned problem? Any feedback is welcome and thanks in advance.

Best regards,

Kimmy. Received on Wed Jun 22 1994 - 04:43:04 CEST