Re: Tracking down NOLOGGING objects
- From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
- To: finn.oracledba@xxxxxxxxx
- Date: Thu, 4 Sep 2008 19:58:31 -0600
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:
