Is there a limit on how much work I can do in one Oracle transaction?

Question

I need to update three tables in one transaction. I use Oracle Spatial, which traverses potentially hundreds of thousands of records. To maintain cross-reference integrity, I really need to update three tables with large amounts of cross-references in a single transaction.

Is there a measurable limit of what I can do in one transaction? Are there any pitfalls to score thousands of insert / update / delete operations in one transaction? For pitfalls, are there any well-known patterns / methods for processing them?

Answer 1

No, there are no limits to the amount of work you can do in one transaction, assuming that you provide enough UNDO space to post the changes. Of course, if you have a lot of people making transactions at the same time, and you have a chance that several people will try to influence the same line, long-term transactions can lead to a lock conflict. And if your transactions require a person’s participation in everything (i.e. your transaction is a person who tries to launch a conveyor over a geographic region in various ways), one long-term transaction can create problems when a person wants to leave work before they are completely fulfilled.

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

Answer 2

the limit is set by the size of the undo tablespace. Each transaction change must fully fit into the undo tablespace.