Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: no logging clause

Re: no logging clause

From: <>
Date: Thu, 29 Nov 2007 20:57:11 -0800 (PST)
Message-ID: <>

On Nov 30, 3:11 pm, Peter Teoh <> wrote:
> According to:
> tables can be created with nologging clause, so that its DDL/DML
> activities are not logged into the redo log. So my question is:
> how do I enumerate a list of all existing objects that have nolog
> status tagged to them?
> Thank you in advance.

select table_name from dba_tables where logging='NO';

There's an equivalent query possible for indexes in dba_indexes, of course.

Bear in mind that the documentation you quoted is misleading: a table or index that has NOLOGGING set will ALWAYS log regular inserts, updates and deletes. The nologging attribute is only 'respected' by a special group of DML/DDL commands (such as CREATE INDEX, CREATE TABLE AS SELECT and certain types of SQL Loader loads). The rule-of-thumb is that if it's possible to get the data back from external or other means, then it is not necessary to protect the data with redo, so nologging can apply if you want it to. But data that's not recoverable by other means is always protected by redo, no matter how many times you state 'nologging'.

An index, for example, can always be reconstructed provided the table data it's built on is safe, so the creation of the index doesn't itself absdolutely need to produce redo, hence create index will respect nologging if it's set. Similarly, create table as select * from other_table is self-recovering: if "table" is destroyed, "other_table" is still available to re-provide the lost data, so redo isn't an absolute necessity... so CTAS will respect the nologging attribute if it's specified.

But if you command starts "INSERT into..." or "DELETE from..." or "UPDATE...", then redo is always, always generated. Received on Thu Nov 29 2007 - 22:57:11 CST

Original text of this message