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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
