Home » RDBMS Server » Server Utilities » SQL loader error while using parallel in direct path
SQL loader error while using parallel in direct path [message #646262] Wed, 23 December 2015 07:50 Go to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
Hi,

While loading the mulitiple files through sql loader, i was getting below error
SQL*Loader-951: Error calling once/load initialization
ORA-26002: Table T1 has index defined upon it.

Later i used SKIP_INDEX_MAINTENANCE=TRUE to avoid but this have disabled index under my table T1 , is there a way to avoid disabling the indexes i mean to use direct and parallel load

ctl file
 
LOAD DATA
INFILE '/path/1.dat'
INFILE '/path/2.dat'
BADFILE '/path/1.bad
DISCARDFILE  '/path/1.dsc'
INTO TABLE T1
APPEND
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ID,
date_col);
 
sqlldr userid=user/pass@db control=/path/test.ctl parallel = true direct = true SKIP_INDEX_MAINTENANCE = TRUE log=/path/.$(date +"%Y%m%d_%H%M%S").log errors=100000

Re: SQL loader error while using parallel in direct path [message #646268 is a reply to message #646262] Wed, 23 December 2015 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
is there a way to avoid disabling the indexes i mean to use direct and parallel load


Drop them. Smile
More seriously the answer is no.

Re: SQL loader error while using parallel in direct path [message #646274 is a reply to message #646262] Wed, 23 December 2015 09:33 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
OK so whats the best way to perform the load very fast using sql loader
Re: SQL loader error while using parallel in direct path [message #646276 is a reply to message #646274] Wed, 23 December 2015 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Direct parallel (assuming your hardware accepts it and there are a lot of rows to load).
It is far faster to rebuild the indexes at the end than to let them updated by the loader.

Re: SQL loader error while using parallel in direct path [message #646278 is a reply to message #646276] Wed, 23 December 2015 10:00 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
so you mean to say i will use the option which i have used SKIP_INDEX_MAINTENANCE=TRUE and later then rebuild the index, but if there are any duplicate records that will result issue in rebuilding
Re: SQL loader error while using parallel in direct path [message #646279 is a reply to message #646278] Wed, 23 December 2015 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26719
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/database/121/CNCPT/datainte.htm#CNCPT1642

UNIQUE constraint is different from unique index.
You can have non-unique INDEX maintain UNIQUE constraint
Re: SQL loader error while using parallel in direct path [message #646280 is a reply to message #646279] Wed, 23 December 2015 10:06 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
The table has primary key
Re: SQL loader error while using parallel in direct path [message #646281 is a reply to message #646280] Wed, 23 December 2015 11:07 Go to previous message
BlackSwan
Messages: 26719
Registered: January 2009
Location: SoCal
Senior Member
rohit_shinez wrote on Wed, 23 December 2015 08:06
The table has primary key

You can use non-unique index to maintain Primary Key
Previous Topic: how to import data over network
Next Topic: Sqlloader password encryption
Goto Forum:
  


Current Time: Thu Jul 02 17:18:27 CDT 2020