How big can/should a transaction be?
I'm working on a new DB persistence using Oracle which will save very big data (multi GB in some cases), because we'll save large blobs (there will also be many rows across many tables). When the user hits save, should I save everything, BLOB included, to the DB in a single transaction? What are the limitations involved in transaction size? Where does uncommitted data go to before its committed? TEMP table space? What happen if I exhaust that? Is it a recoverable error I can get a callback for in OCI on the client side? If I'm forced to break the same into multiple transactions, how to back out the whole thing in case of an error?
Obviously I'm new to these kinds of questions, and I'm happy to get suggestions on which parts of the doc I should be reading to get answers. (I'm using 11g).
But I'd also be grateful for any insights into managing very large transactions.
Thanks, --DD
PS: BLOB as saved into SECUREFILEs, not BASICFILEs, in case that makes a difference.