Re: Tracking down NOLOGGING objects

From: Mark Brinsmead <>
Date: Mon, 1 Sep 2008 21:13:31 -0600
Message-ID: <>

I'm not sure whether Oracle tracks NOLOGGING operations at the segment level. I've never really bothered to look.

But it *does* track them at the *block* level! If you have a physical standby, at least. Blocks affected by non-logged operations will manifest there as "corrupt" blocks. Something like an RMAN "BACKUP VALIDATE" will be sufficient to find them; they will be reported both in the RMAN output and a dictionary view (V$BLOCK_CORRUPTION maybe). From corrupt blocks it is a short but tedious journey to segments. Assuming the dictionary view doesn't provide you with the segment name, that is. (Sorry -- no documentation on hand here -- just an aging memory.)

I know that this is not quite what Finn has been thinking of, but it might need his needs.

On Mon, Sep 1, 2008 at 5:58 PM, Andrey.Kriushin <>wrote:

> Hi Finn,
> Mark's comment on feasubility is complete. Nothing to add.
> However for pure theoretical investigation... At least we know now, that
> Oracle RDBMS doesn't keep a track of NOLOGGING operations at the segment
> level. Does it?
> I'd look at the definition of GV$SEGMENT_STATISTICS and x$ksolsfts. This
> x$-table keeps the runtime statistics (FTS_STATNAM), and if you see
> 'physical writes direct' there, it might be (or not - in case of PQO) an
> indication of direct load, probably with the NOLOGGING.
> BTW, it would be nice to see the version of the product (RDBMS) in your
> future posts.
> HTH,
> -- Andrey
> ...

-- Mark Brinsmead
Senior DBA,
The Pythian Group

Received on Mon Sep 01 2008 - 22:13:31 CDT

Original text of this message