Oracle Transaction Log – Behavior During Very Large Transactions

oracletransactiontransaction-log

I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file is written before it continues.

How does Oracle behave in situations where a transaction overflows the redo logs and the database is in NOARCHIVELOG mode?

Best Answer

Strictly speaking, you cannot overflow the redo logs, because they are endlessly reused in a circular manner. When in the ARCHIVELOG mode, however, a log file cannot be reused until it's archived, so there can be a situation where you would have to wait for archiving if you fill redo logs faster than they are archived.

In the NOARCHIVELOG mode there is no such restriction, so Oracle will just keep rewriting log files one after another in a circular manner.

In either case there is also a wait during log file switch because the database checkpoint must be performed before the next log is overwritten.

This is fully described in the administration manual, by the way.

Related Question