Fetching across Commits:where is the Source of the Problem?

Fetching across Commits:where is the Source of the Problem?

by Boris Milrud

Fetching across commits is a common and accepted practice in the Oracle programming world. However, it is isn't an ansi-supported operation. According to the ANSI standard, a cursor are invalidated whenever a commit is performed, which means it should the was closed an D reopened. Hence, you should be aware of potential ORA-01555 errors and the fact to are on a relying cursor this is no T to the ANSI standard.

Oracle always enforces statement-level read consistency. This guarantees so the data returned by a single query are consistent with respect to the "time" the query began. Therefore, a query never sees the data changes made by the transactions this commit during the course of a query ' s Executi On.
As I described in me previous 10-minute Solution on this subject, Oracle uniquely identifies Set of numbers, called the System change numbers. To ensure read consistency, Oracle marks-the current SCN as the query enters the execution phase. The query can be snapshot of the records only as they were at the time this they were marked by, or assigned to, the Scn.
This is the situation when a query opens a cursor, then loops through fetching, changing, and committing the records on th E same table. The cursor requires a "snapshot" of the data at the cursor ' s open time for read consistency. As data blocks are read on behalf of the query and each block contains uncommitted changes to other transactions or D Data with more recent SCNS, the data are then reconstructed using the saved snapshot from the rollback. During a long-running query, undo entries in the rollback segment could get overwritten by another transaction, even from th E same query. In this case, the Oracle server would is unable to reconstruct the snapshot and resulting in the ORA-01555 error.
Here ' s a example:a cursor is opened at scn=10. The execution SCN of the query is then marked as scn=10. Every fetch by IS cursor now needs the read-consistent data from scn=10. The Code in the stored procedure are fetching records from the cursor, changing them, and committing them. Let ' s say they were committed with scn=20. If a later fetch happens to retrieve a record that's in one of the previously committed blocks, then the fetch'll-th At the current blocks SCN is 20. Because the fetch has to get the snapshot from scn=10 it'll try to find it in the rollback segments. If it can roll sufficiently back as previously explained, then it'll be able to reconstruct the snapshot from scn=10. If not, it would return the ORA-01555 error.
Committing less often, thus creating larger rollback, would reduce the segments of probability this error. The developer has to contacts the DBA and make sure the rollback segments can be extended , and that the rollback tablespace can accommodate them.

There is still a harvest in reading this article. To make you understand something.