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@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
