Re: Tracking down NOLOGGING objects

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

--
http://www.freelists.org/webpage/oracle-l