RE: Insert ... nologging

From: Vadim Gorbounov <vgorbounov_at_simplyengineering.com>
Date: Thu, 19 Apr 2001 13:34:18 -0700
Message-ID: <F001.002ED33C.20010419133028@fatcity.com>

Yosi, I suggest you the following syntax for direct path insert with nologging option:

insert /*+ APPEND NOLOGGING */ into tab1 select .... from tab2;

The word NOLOGGING in your first insert is treated as table alias (and really does nothing with nologging), i believe, otherwise the statement is invalid.

HTH Vadim Gorbounov
Oracle DBA

-----Original Message-----
Sent: Thursday, April 19, 2001 4:48 PM
To: Multiple recipients of list ORACLE-L

Hello all,

I'm trying to do inserts with NOLOGGING. It works if I don't specify insert columns OR if I have no where clause. If I have EITHER insert column specification OR a where clause, Oracle can't interpret the SQL.

HELP!!!! PLEASE!! The following syntax works fine:

• 1. This has a where clause in the select, and no insert
• column specification. Nologging is up top. INSERT into table1 -- defined as table1 (a varchar2 (5)) NOLOGGING SELECT dummy FROM dual WHERE dummy = 'X';

(Don't know if it actually skips logs, but I imagine it does. But at least it inserts.)

Following statement also works:

• 2. This has column specifications but no where clause.
• Nologging is moved to the end of the statement. INSERT into table1 (a) SELECT dummy FROM dual NOLOGGING;

The following, however, I can't get to work.

• 3. This HAS a where clause AND column specification,
• with Nologging at the end of the statement. INSERT into table1 (a) SELECT dummy FROM dual WHERE dummy = 'X' NOLOGGING; SQL> / NOLOGGING * ERROR at line 5: ORA-00933: SQL command not properly ended

Nologging is (seemingly) interpreted as some part of the where clause, and Oracle does not understand it. Moving nologging up, does not work either, as follows:

• 4. This HAS a where clause AND column specification,
• and Nologging before the select clause. INSERT into table1 (a) NOLOGGING SELECT dummy FROM dual WHERE dummy = 'X'; SQL> / NOLOGGING * ERROR at line 2: ORA-00926: missing VALUES keyword

Can anyone shed any light here?

Thanks in advance, as always,

Yosi
yosi_at_comhill.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Yosi_at_comhill.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vadim Gorbounov
  INET: vgorbounov_at_simplyengineering.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Apr 19 2001 - 15:34:18 CDT