Re: Suppress writing Redo Log Data

From: Howard J. Rogers <howardjr_at_www.com>
Date: Wed, 15 Nov 2000 03:01:07 +1100
Message-ID: <3a1161f5@news.iprimus.com.au>

Hi David,

I think you may have the cart and the horse round the wrong way. NOLOGGING doesn't suppress redo for normal inserts, updates and deletes, but only for peculiar DML done under certain circumstances (eg, create table newemp as select * from oldemp, SQL*Loader in direct path and so on). Various DDL can also be suppressed (such as create indexes, and indeed, create table).

I only mention it because the doco for 8.1.5 states (as it has done since at least 8.0.5):

Quote ON:::::
Although you can set the NOLOGGING attribute for a table, partition, index, or tablespace, no-logging mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute. Only the following operations can make use of no-logging mode:

direct load (SQL*Loader)
direct-load INSERT
CREATE TABLE ... AS SELECT
CREATE INDEX

ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... SPLIT PARTITION
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION

INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

QUOTE OFF:::::::::::::::::::::::

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------


"David Fitzjarrell" <oratune_at_aol.com> wrote in message
news:8urlvs$3s1$1_at_nnrp1.deja.com...


> In our last gripping episode insued_at_my-deja.com wrote:

> > Hello,

> >

> > Is it possible to tell Oracle *not* to write redo log information for

 certain


> > insert and update statements ? There is no need for the modified data

 to be


> > recovered after DB crash or DB shutdown, so the redo data is not

 really


> > necessary.

> >

> > I appreciate any help.

> >

> > Thanks,

> > Thomas Staudenmaier

> >

> > Sent via Deja.com http://www.deja.com/

> > Before you buy.

> >

>

> Not on a statement basis.  There is an option to CREATE TABLE/ALTER

> TABLE (also applies to CREATE/ALTER INDEX statements) that will prevent

> most redo log entries (inserts, deletes, updates) from being written

> but it is at the table/index level.  INVALID blocks and dictionary

> changes (add table columns, alter column storage parameters, drop table

> columns [in 8i]) will still be written to the redo logs but garden

> variety DML will not.

>

> Check the Oracle documentation for more on the NOLOGGING option for

> tables and indexes.

>

> --

> David Fitzjarrell

> Oracle Certified DBA

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Tue Nov 14 2000 - 10:01:07 CST