Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> re NOLOGGING -- was re RE: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against
are meant to say the the first data load is NOLOGed. All subsequent *regular* DML is logged. That doesn't mean
- 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.
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@singnet.com.sg]
- Sent: Tuesday, August 23, 2005 8:56 AM
- To: jkstill@gmail.com; Allen, Brandon
- Cc: cmarquez@collegeboard.org; oracle-l@freelists.org
- 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
-- http://www.freelists.org/webpage/oracle-l Received on Wed Aug 24 2005 - 08:06:07 CDT