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>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
>
>   9  and 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

Other related posts: