Is there a limit to how much work I can do in a single Oracle transaction?

Question

I have to update three tables in a single transaction. I'm using Oracle Spatial intersecting against potentially hundreds of thousands of records. To maintain cross-reference integrity, I really need to update three tables with large volumes of cross-references in a single transaction.

Is there a measurable limit to how much I can do in a single transaction? Are there pitfalls to cramming thousands of insert/update/delete operations in a single transaction? For the pitfalls, are there any well-known patterns/practices for handling them?

Answer 1

No, there is no limit to the amount of work you can do in a single transaction, assuming you provide enough UNDO space to accomodate the change. Of course, if you have many people doing transactions at the same time and you have the possibility that multiple people will be trying to affect the same row, long-running transactions can introduce lock contention. And if your transactions require human input throughout (i.e. your transaction is a human trying various ways to run a pipeline over a geographic region), a single long-running transaction may create problems where the human wants to leave work before they are completely done.

Oracle Workspace Manager is a component of the Oracle database that was specifically created to handle these sorts of very long-running transactions on large spatial data sets by allowing users to work in separate workspaces that can be merged back into the parent workspace days or weeks later. The semantics of Workspace Manager are very similar to the semantics of transactions but provide the ability to leave and rejoin a workspace, to switch between workspaces, and to have a hierarchy of child workspaces.