ORA-22924

[aehimself]

Theoretical question, I think I know the answer I'm just looking for confirmation and possible solutions.

I have a READ-ONLY worker thread, which processes a large number of CLOB data. LOBs are loaded upon access, not when opening the dataset. After ~2 days of continuous running I was greeted with the following error:

Other OCILobRead2 ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old (#1555)

All of my worker threads derive from the same base thread, which (as most of my workers do updates in the database as well) sets the ZConnection.AutoCommit to false before connecting and therefore enables transactions. What I understand from this error is that it appears when a transaction is too long, we should commit / roll back more often. This is easily the source at me since I had a transaction which is ~2 days old.

Now, if I override this functionality and set AutoCommit to True in this worker, will it solve my issue? Or should I do a Connection.Rollback every 30 minutes...? This feels kinda hacky...

Any opinion is welcome :)

[marsupilami]

An equally theoretical answer :
I seem to remember that Egonhugeist told me that Oracle doesn't have a true AutoCommit mode and so Zeos somehow simulates it. In that case I wouldn't trust the emulation too much and do a commit or rollback from time to time myself.

[EgonHugeist]

Jan, there is no emulation about "autocommit" for Oracle. It's simply not supported see: https://docs.oracle.com/cd/E11882_01/ap ... LNOCI17163 patameter "mode". we just send OCI_COMMIT_ON_SUCCESS if the connection is in AutoCommit mode.

[marsupilami]

we just send OCI_COMMIT_ON_SUCCESS if the connection is in AutoCommit mode.

I assume, this is what I meant when I said:

Oracle doesn't have a true AutoCommit mode and so Zeos somehow simulates it.

But then - to me it seems that using OCI_COMMIT_ON_SUCCESS with OCIStmtExecute comes pretty close to a true autocommit mode - so using it should be quite safe when compared with the things we once did with dblib and Firebird...