Skip to Main Content
  • Questions

  • table nologging and redo /undo consumption

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Tom Kyte

Thanks for the question, ted.

Asked: August 01, 2000 - 12:47 pm UTC

Last updated: December 23, 2011 - 2:49 pm UTC

Version: 8i

Viewed 10K+ times! This question is

You Asked

thomas,

If a table is altered with nologging option, would subsequent delete/update consumes less redo/undo (similar to direct insert) ? I posted this question at deja and some people tells me yes. when I did test found from v$sysstat, there is no difference in redosize, redo writes and redo blocks written before and after 'alter table nologging;.

thnx in advance,
ted chyn

and Tom said...

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:366018048216 <code>for more info. The very very short answer is NO.

Normal inserts/updates/deletes on the table are logged as normal. nologging only affects certain operations like direct path loads and such on the object.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 22, 2006 - 4:45 pm UTC


A reader, December 23, 2011 - 2:41 pm UTC

"Normal inserts/updates/deletes on the table are logged as normal. nologging only affects
certain operations like direct path loads and such on the object." <from your first answer>

1. My database tablespces are NOLOGGING. and ARCHIVELOG enabled

2. Did a RMAN hot backup on source database 11gR2
3. moved the backup to target database
4. restored DB no errors
5. Applied archive logs (moved from source db)
6. About 100 of them applied. Then got error
Redo is inconsistent with data block

Please tell me if I am correct

1. The NOLOOGING will affect recovery only if there is direct load like insert APPEND. or SQL Loader direct
Otherwise recovery is not an issue with NOLOGGING

2. If the database shutdown and started on MOUNT state
since DB is consistent, DUPLICATE database from source to target will work successfully even if NOLOGGING and DIRECT load in source DB

3. I am sure you are not in favor of this, but for sake of completeness. cold RMAN backup and restore to target will work successfully even if NOLOGGING and DIRECT load in DB

Thanks
Tom Kyte
December 23, 2011 - 2:49 pm UTC

nologging would not produce that sort of error, that is more likely a corrupt redo log file - apply one of your multi-plexed copies of that redo log file.


1) it won't affect the recovery actually, it will affect the ability to retrieve the data after recovery. Anything that was NOT in the redo log files will not be in the datafiles. It wouldn't break the recovery process. It would cause errors later when selecting the data.

2) yes, because it would read the datafiles - which have all of the data in them. The redo would not be needed.

3) correct.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.