Tag Archives: Nologging

ORACLE – redo-log by using append-hint, nologging and archive-log-mode

Here a very good overview about the combinations when Redo-Log is generated or not for inserts with and without logging/append-hint/archive-log-mode. Found by asktom.

Check DB for archive-log-mode turned on or off:
select log_mode from v$database;

Check for logging-mode for table:
select logging from all_tables
where owner = '<USER>'
and table_name = '<TABLE_NAME>';

Example for Direct-Path-Insert with append-hint:
insert into '<TABLE_NAME>' /*+ append */
select * from all_objects;


TableMode  Insert Mode ArchiveLogMode  result
---------- ----------- --------------- ----------
LOGGING    APPEND      ARCHIVE LOG     redogenerated
NOLOGGING  APPEND      ARCHIVE LOG     no redo
LOGGING    no append   ""              redogenerated
NOLOGGING  no append   ""              redogenerated
LOGGING    APPEND      noarchive       no redo
NOLOGGING  APPEND      noarchive       no redo
LOGGING    no append   noarchive       redogenerated
NOLOGGING  no append   noarchive       redogenerated

If “FORCE LOGGING” is used, than of course redo is generated!