Oracle Database Discussions

Hello.

I think this is a basic thing (release 8.1.7.4). I must say I don't know how rollback segments really work.

A table, where new records are continuously inserted and the old ones can be updated in short transactions, should be purged every day by deleting old records.

This purge has never been done and as a result it has now almost 4 million records, and when I launch the stored procedure that deletes the old records I get the "snapshot too old" error because of the read consistency.

If I launch the procedure after stopping the application that inserts and updates in the table, then I don't get the error. I guess the problem is that meanwhile the procedure is being executed other transactions also need to use rollback segments so that the rollback segment space that the snapshot needs isn't enough. Do you think this is the problem?

If this is the case then I suppose that the only solution is increasing the size of the only datafile of the only tablespace for my 4 rollback segments. Am I wrong?


(Three more questions:
- Could the problem be solved by locking some rollback segments for the snapshot? How could I do that?
- What is a discrete transaction?
)


I'm a developer, not a dba, but don't tell me to ask my dba because it isn't that easy. Thanks in advance.