In an Oracle-l thread, this question was raised: how can you find objects creating nologging changes?

First, what is a ‘nologging’ change?

The redo logging mechanism plays critical role in media recovery. Media recovery relies on archivelog files generated to roll the database forward. A standby database or dataguard recovery also relies on archivelog files. It is possible to do DML changes with minimal logging, i.e., nologging changes, also known as direct mode inserts. For example, insert /*+ append */ can be used to populate the rows into a table without generating much redo. This can invalidate the standby database and might trigger rebuilding some or all parts of the standby database.

nologging changes generates minimal redo, since the blocks are pre-formatted and written to disk directly. A redo record is generated, invalidating a range of affected blocks. This invalidation redo record size is far smaller, for e.g. hundreds of blocks can be invalidated using just a single redo record. Of course, recovery is severely affected as the changes performed with nologging operations cannot be reapplied/recovered.

Internals of nologging changes

Since nologging is all about redo records, dumping the redo log file or archivelog file is a concrete way to see what happens under the hood. Let’s consider an example to explain the internals of nologging changes. We will create a table, insert rows, and closely review the redo records.

(more…)

We hit an ORA-01450 error today trying to do online rebuild for an index in an unusable state. This was a non-unique index on a fairly large column — VARCHAR2(800 CHAR).

SQL> alter index i1 rebuild online;
alter index i1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

It rang a bell. I remembered that I encountered this issue a while ago, but I couldn’t recall the details. I know that it has nothing to do with the actual data size — it’s an error that can occur during index creation. A single index block must be able to fit at least two index entries, so the maximum index key size defined by the block size and overhead.

The key length is calculated as:

key length = sum of all column lengths
           + number of columns
           + 2 (key length)
           + 6 (restricted ROWID)
           + 1 (ROWID field length)

If I didn’t miss anything, the key length for my index should be 800 + 1 + 2 + 6 + 1 = 810. Hold on . . . we have character length semantic here — let’s check the database character set:

(more…)