RE: Tracking down NOLOGGING objects

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,

-Mark

________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Mark Brinsmead [pythianbrinsmead@xxxxxxxxx]
Sent: Thursday, September 04, 2008 9:58 PM
To: finn.oracledba@xxxxxxxxx
Cc: yong321@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Tracking down NOLOGGING objects

Finn,

   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 
<finn.oracledba@xxxxxxxxx<mailto:finn.oracledba@xxxxxxxxx>> 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 = df.name<http://df.name>

  9  and ts.name<http://ts.name> = 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.



Thanks,

Finn





--
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs
--
http://www.freelists.org/webpage/oracle-l

Other related posts: