RE: Tracking down NOLOGGING objects

From: Bobak, Mark <>
Date: Thu, 4 Sep 2008 23:17:23 -0400
Message-ID: <>

Hi Mark,

While there are other commands that can change logging mode for an object, INSERT /*+ APPEND */ is not one. The APPEND hint will enable direct path insert. If nologging is set on the table, then redo writes are (mostly) avoided, in addition to the avoidance of undo writes. However, something like "INSERT /*+ APPEND */ my_table nologging" will not alter the logging mode of my_table to nologging. Instead, you get a table alias, "nologging", for my_table.

Other commands, however, such as:
alter index .... rebuild nologging;
create table my_table nologging as select * from my_other_table; (and there are others, mostly all the DDL command forms)

will all create the object as nologging, or alter the object from logging to nologging.

Hope that helps,


From: [] On Behalf Of Mark Brinsmead [] Sent: Thursday, September 04, 2008 9:58 PM To:
Cc:; Subject: Re: Tracking down NOLOGGING objects


   Remember that setting LOGGING mode on a segment merely establishes a default. SQL*Loader is far from the only thing that can circumvent this default. I think you will find similar results, for example with INSERT /*+APPEND*/ ... NOLOGGING.

   This is why FORCE_LOGGING mode exists...

On Wed, Sep 3, 2008 at 2:23 PM, Finn Jorgensen <<>> wrote: FOLLOW UP: Many of the suggestions in this thread has been extremely informative and very useful in my quest to find out what's going on in my database (large company, most things are clear as mud!)

In testing this it turns out the unrecoverable_XXXX columns in v$datafile are only set if the database is in archivelog mode. I wasn't aware of that. It also turns out that using sqlload to do direct AND unrecoverable (both have to be used) dataloads will set the values in v$datafile even though LOGGING=YES on the object I'm loading into.

I came up with a couple of useful queries during all this. For example the below query lists all objects in tablespaces belonging to datafiles that have received unrecoverable data where the object has received "physical writes direct" since instance startup.

  1 select o.owner,o.object_name,dfs.tablespace_name,s.value, t.logging

  2 from v$segstat s, v$tablespace ts, dba_objects o, dba_tables t, v$datafile df, dba_data_files dfs

  3 where s.statistic_name = 'physical writes direct'

  4 and o.object_id = s.obj#

  5 and ts.ts# = s.ts#

  6 and s.value != 0

  7 and df.unrecoverable_change# != 0

  8 and dfs.file_name =<>

  9 and<> = dfs.tablespace_name

 10 and t.owner = o.owner

 11 and t.table_name = o.object_name

 12* order by 2,3,1

In my case unrecoverable sqlloads were the culprit.




-- Mark Brinsmead

Senior DBA,
The Pythian Group
-- Received on Thu Sep 04 2008 - 22:17:23 CDT

Original text of this message