re NOLOGGING -- was re RE: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against

From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>
Date: Wed, 24 Aug 2005 21:03:53 +0800

um.
The lines

When an object is NOLOGGING is used in the CREATE statement, it is the actual first load that the create does

that is NOLOGGING. All subsequent DML is logged.

are meant to say the the first data load is NOLOGed. All subsequent *regular* DML is logged. That doesn't mean
that operations that NOLOGGING supports are LOGged.
I was differentiating a CREATE <LOGGING> from a CREATE NOLOGGING.
Thus
   a) CREATE TABLE x AS SELECT * FROM y     ==> Everything Logged
   b) CREATE TABLE x AS SELECT * FROM y NOLOGGING ==> only Data Dictionary updates are Logged
   c) In either of the above, ALTER TABLE NOLOGGING ==> subsequent operations that support NOLOGging are NOLOGged

I also referenced Note 147474.1 in my email to specifically list the operations that support NOLOGging.
{except that I inadvertently typed 17474.1 in that email}

Direct Path Loads (APPEND Hint) aren't exactly the same as the example a) above ---- they actually update the HWM first and directly
format data blocks.

Hemant
At 05:05 AM Wednesday, Allen, Brandon wrote:

Hemant,

Where in the documentation do you see a description of this different treatment of the NOLOGGING clause for CREATE vs. ALTER statements?

My understanding from the documentation, including that which you have included in your email below, is that when specifying NOLOGGING during either the CREATE or ALTER of an object, the creation/alteration of the object as well as any future direct loads/inserts are minimally logged. If you see something contrary to this in the documentation, please provide a link.

Thanks,
Brandon

P.S. in case anyone else tries to look it up - the correct Metalink note # is 147474.1

-----Original Message-----

From: Hemant K Chitale [ mailto:hkchital@xxxxxxxxxxxxxx]

Sent: Tuesday, August 23, 2005 8:56 AM

To: jkstill@xxxxxxxxx; Allen, Brandon

Cc: cmarquez@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx

Subject: Re: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against

Jared,

You ARE right. You really don't need to retest it.

When an object is NOLOGGING is used in the CREATE statement, it is the actual first load that the create does

that is NOLOGGING. All subsequent DML is logged.

When an object is ALTERed to NOLOGGING, only certain statements (eg APPEND) that support NOLOGGING

are NOLOGGed.

Read the extract that Allen has quoted "Direct Loader .. and direct path insert". It specifically talks of Direct

operations NOT regular DML. Regular DML is always always LOGged.

Again, quoting from the 9i SQL Reference on the logging clause : This also says " .. subseqent direct .. direct ..."

NOT regular DML.

Note 17474.1 , from Oracle7 and Oracle8 days is still the best reference.

   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

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

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

RE: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against
From: Allen, Brandon

Other related posts:
» re NOLOGGING -- was re RE: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against