|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Force logging and nologging mode [Oracle]
A database is either in force logging or no force logging mode. Use v$database.force_logging to determine in which it is.
Yet to be finished ..
Nologging can be used to minimize the amount of redo generated.
Only the following statements can make use of nologging:
the +append hint
Additionally, a direct load with SQL*Loader and a direct load insert can also make use of nologging. The direct load insert is a special form of the insert statement that uses the /*+ append */ hint.
Note: Even though direct path load reduces the generation of redo, it is not totally eliminated. That's because those inserts still generate undo which in turn generates redo.
That means that ordinary inserts, updates and deletes always generate redo, no matter if the underlying table or index is specifed with nologging or not.
If there is an index on the table, and an +append insert is made on the table, the indexes will produce redo. This can be circumvented by setting the index to unusable and altering the session's skip_unusable_indexes to true.
Nologging can be overriden at tablespace level using alter tablespace ... force logging.