[Q] sql loader problem while load record more than one line???
We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: [Q] sql loader problem while load record more than one line??
This is a hack but ... you might want to look into ftp-ing a file to Unix, run a 'tr' or 'sed' to get rid of the EOL character. Thanks, Nikhil -Original Message- Sent: Thursday, January 29, 2004 10:34 AM To: Multiple recipients of list ORACLE-L We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Nikhil Khimani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: [Q] sql loader problem while load record more than one line???
So, why don't you use migration workbench, when it's available? On 01/29/2004 10:34:27 AM, dba1 mcc wrote: We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: [Q] sql loader problem while load record more than one line??
Thank you for answer. I did not said clearly. Most records are fine. ONly some records have this problem. re-transfer from PC to UNIX will not fix problem. --- Nikhil Khimani [EMAIL PROTECTED] wrote: This is a hack but ... you might want to look into ftp-ing a file to Unix, run a 'tr' or 'sed' to get rid of the EOL character. Thanks, Nikhil -Original Message- Sent: Thursday, January 29, 2004 10:34 AM To: Multiple recipients of list ORACLE-L We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Nikhil Khimani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: [Q] sql loader problem while load record more than one line???
You may want to add CONTINUEIF clause and try the load again ... infile 'data.asc' CONTINUEIF NEXT (1) != '' into table test ... For complete reference, see this URL: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1005518 -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ dba1 mcc wrote: We are migrate from MS Access to ORACLE(9.2.0.4) use SQL*Loader. The problem we have are some of MS Access dump records (ASCII output) have to several lines(EOL before end of record). For example: control file: load data infile 'data.asc' into table test fields terminated by ','optionally enclosed by '' TRAILING NULLCOLS ( ID, comment ) Data.asc file: 101, September 1, 2003 - Labor Day November 27, 2003 - Thanksgiving Day November 28, 2003 - Day after Thanksgiving December 24, 2003 - Christmas Eve December 25, 2003 - Christmas Day January 1, 2004 - New Year's Day January 19, 2004 - Martin Luther Ki Does there has way to pass around? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
named pipes, sql loader and ftps
I was flipping through Tim Gorman's Data Warehouse and he has a short section on using named pipes to kick off sql loader when you send files to your data warehouse with ftps. Ive never used named pipes in Unix? Does it depend on the shell(I only know korn). Anyone do this? With this method you can start doing your sqlloader while still recieving your file. Anyone ever do this with automated file reciept? Files come in over night and the sqlloader gets kicked off?
Re: named pipes, sql loader and ftps
Comments inline On 01/15/2004 04:14:27 PM, Ryan wrote: I was flipping through Tim Gorman's Data Warehouse and he has a short section on using named pipes to kick off sql loader when you send files to your data warehouse with ftps. Tim is a resourceful guy. I scripted it once for export. Ive never used named pipes in Unix? Does it depend on the shell(I only know korn). No, it doesn't depend on the shell. You create named pipe by using mknod pipe name p. Some Unix versions have specialized command called mkpipe. Anyone do this? Yup. With this method you can start doing your sqlloader while still recieving your file. Anyone ever do this with automated file reciept? Files come in over night and the sqlloader gets kicked off? Since the advent of the external tables, loader is no longer a must. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL*Loader to load mutiple languages from the single datafile
Dear All, Happy New Year! I have to load both French and Japanese characters from the same datafile into tables using SQL*loader. DB character set is UTF8 (version 8.1.7.3) Client : Unix (Sun Solaris) If I set NLS_LANG=AMERICAN_AMERICA.UTF8 then French characters are NOT getting loaded properly(garbled- non-readable format) but Japanese characters are getting loaded without any problem. If I set NLS_LANG=French then French characters are getting loaded properly( no garbled issue) but Japanese characters are NOT getting loaded(garbled). French Data === Lafort Franois modle dmineur Japanase Data === ? ?? I copied sample FRENCH JAPANESE characters above. Even here in BROWSER, (A)if the BROWSER Encoding (please select View-Encoding) is set to Unicode(UTF-8)then Japanese Data (characters) are in READABLE format. (B)if the BROWSER Encoding (please select View-Encoding) is set to Wester European(ISO),then French Data (characters) are in READABLE format. Questions: == 1)Why french characters are not getting loaded properly when I set NLS_LANG=AMERICAN_AMEICA.UTF8 2)How do I load mutiple langauges content from the same datafile into db table. Kindly through some light on this? Thanks for your time. Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL*Loader to load mutiple languages from the single
When the French characters are loaded properly, are you setting NLS_LANG to FRANCE_FRENCH.UTF8, or are you using a different character set? Justin Cave At 01:39 PM 1/2/2004, [EMAIL PROTECTED] wrote: Dear All, Happy New Year! I have to load both French and Japanese characters from the same datafile into tables using SQL*loader. DB character set is UTF8 (version 8.1.7.3) Client : Unix (Sun Solaris) If I set NLS_LANG=AMERICAN_AMERICA.UTF8 then French characters are NOT getting loaded properly(garbled- non-readable format) but Japanese characters are getting loaded without any problem. If I set NLS_LANG=French then French characters are getting loaded properly( no garbled issue) but Japanese characters are NOT getting loaded(garbled). French Data === Laforêt François modèle démineur Japanase Data === çµãè¾¼ã¿å±¥æ´ çµãè¾¼ã¿ã®è¨å® I copied sample FRENCH JAPANESE characters above. Even here in BROWSER, (A)if the BROWSER Encoding (please select View-Encoding) is set to Unicode(UTF-8)then Japanese Data (characters) are in READABLE format. (B)if the BROWSER Encoding (please select View-Encoding) is set to Wester European(ISO),then French Data (characters) are in READABLE format. Questions: == 1)Why french characters are not getting loaded properly when I set NLS_LANG=AMERICAN_AMEICA.UTF8 2)How do I load mutiple langauges content from the same datafile into db table. Kindly through some light on this? Thanks for your time. Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL*Loader to load mutiple languages from the single
Justin,Thanks for your reply. When the French characters are loaded properl I did set NLS_LANG=French -Original Message- Justin Cave Sent: Friday, January 02, 2004 7:14 PM To: Multiple recipients of list ORACLE-L When the French characters are loaded properly, are you setting NLS_LANG to FRANCE_FRENCH.UTF8, or are you using a different character set? Justin Cave At 01:39 PM 1/2/2004, [EMAIL PROTECTED] wrote: Dear All, Happy New Year! I have to load both French and Japanese characters from the same datafile into tables using SQL*loader. DB character set is UTF8 (version 8.1.7.3) Client : Unix (Sun Solaris) If I set NLS_LANG=AMERICAN_AMERICA.UTF8 then French characters are NOT getting loaded properly(garbled- non-readable format) but Japanese characters are getting loaded without any problem. If I set NLS_LANG=French then French characters are getting loaded properly( no garbled issue) but Japanese characters are NOT getting loaded(garbled). French Data === Laforêt François modèle démineur Japanase Data === çµãè¾¼ã¿å±¥æ´ çµãè¾¼ã¿ã®è¨å® I copied sample FRENCH JAPANESE characters above. Even here in BROWSER, (A)if the BROWSER Encoding (please select View-Encoding) is set to Unicode(UTF-8)then Japanese Data (characters) are in READABLE format. (B)if the BROWSER Encoding (please select View-Encoding) is set to Wester European(ISO),then French Data (characters) are in READABLE format. Questions: == 1)Why french characters are not getting loaded properly when I set NLS_LANG=AMERICAN_AMEICA.UTF8 2)How do I load mutiple langauges content from the same datafile into db table. Kindly through some light on this? Thanks for your time. Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Sami INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL*Loader to load mutiple languages from the single
Did you set it just to French, or did you specify a particular character set? From the symptoms you're describing, it sounds like your data file has problems. The French characters may be encoded using a different character set than the Japanese characters. If you want to load the file, you'll have to ensure that everything is encoded with the same character set. Justin Cave At 08:09 PM 1/2/2004, Sami wrote: Justin,Thanks for your reply. When the French characters are loaded properl I did set NLS_LANG=French -Original Message- Justin Cave Sent: Friday, January 02, 2004 7:14 PM To: Multiple recipients of list ORACLE-L When the French characters are loaded properly, are you setting NLS_LANG to FRANCE_FRENCH.UTF8, or are you using a different character set? Justin Cave At 01:39 PM 1/2/2004, [EMAIL PROTECTED] wrote: Dear All, Happy New Year! I have to load both French and Japanese characters from the same datafile into tables using SQL*loader. DB character set is UTF8 (version 8.1.7.3) Client : Unix (Sun Solaris) If I set NLS_LANG=AMERICAN_AMERICA.UTF8 then French characters are NOT getting loaded properly(garbled- non-readable format) but Japanese characters are getting loaded without any problem. If I set NLS_LANG=French then French characters are getting loaded properly( no garbled issue) but Japanese characters are NOT getting loaded(garbled). French Data === Laforêt François modèle démineur Japanase Data === çµãè¾¼ã¿å±¥æ´ çµãè¾¼ã¿ã®è¨å® I copied sample FRENCH JAPANESE characters above. Even here in BROWSER, (A)if the BROWSER Encoding (please select View-Encoding) is set to Unicode(UTF-8)then Japanese Data (characters) are in READABLE format. (B)if the BROWSER Encoding (please select View-Encoding) is set to Wester European(ISO),then French Data (characters) are in READABLE format. Questions: == 1)Why french characters are not getting loaded properly when I set NLS_LANG=AMERICAN_AMEICA.UTF8 2)How do I load mutiple langauges content from the same datafile into db table. Kindly through some light on this? Thanks for your time. Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Sami INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL*Loader and rollback segment
Dear Gurus, I have a problem loading data from flat file using SQL*Loader. The problem is unable to extend rollbacksegment. Is there a way to assign BIG rollback segment to SQL*Loader transaction? If not what is the work around to load huge volume of data without using TRUNCATE option? Thanks Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL*Loader and rollback segment
[EMAIL PROTECTED] wrote: Dear Gurus, I have a problem loading data from flat file using SQL*Loader. The problem is unable to extend rollbacksegment. Is there a way to assign BIG rollback segment to SQL*Loader transaction? If not what is the work around to load huge volume of data without using TRUNCATE option? Thanks Jay One work around might be to create a dedicated Oracle account with the suitable rights (INSERT on the table to load) and to use a login trigger to assign the rollback segment. Another, and probably much better, way would be to have several sessions running and parallel (and hopefully assign to different rollback segments by Oracle). If your volume of data is really big, chances are that you are loading into a partitioned table. If your input data had the good taste of being made of several files, each one destined to a separate partition, would be great. Perhaps some preprocessing is required. Otherwise split your data file, be certain to have several free lists to avoid contention, and there you go. You may have to play with constraints, this is usually the price to pay to do things in parallel. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL*Loader and rollback segment
How about just committing every n rows, instead of trying to fit the whole thing into one transaction? This is why they have the ROWS= option on the command line and in the parameter file. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sat, 15 Nov 2003, Stephane Faroult wrote: [EMAIL PROTECTED] wrote: I have a problem loading data from flat file using SQL*Loader. The problem is unable to extend rollbacksegment. Is there a way to assign BIG rollback segment to SQL*Loader transaction? If not what is the work around to load huge volume of data without using TRUNCATE option? One work around might be to create a dedicated Oracle account with the suitable rights (INSERT on the table to load) and to use a login trigger to assign the rollback segment. Another, and probably much better, way would be to have several sessions running and parallel (and hopefully assign to different rollback segments by Oracle). If your volume of data is really big, chances are that you are loading into a partitioned table. If your input data had the good taste of being made of several files, each one destined to a separate partition, would be great. Perhaps some preprocessing is required. Otherwise split your data file, be certain to have several free lists to avoid contention, and there you go. You may have to play with constraints, this is usually the price to pay to do things in parallel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
AW: Looking for help - sql*loader and truncate
Hi Looks like I have to try that one, since truncate should be reasonably faster then replace. Thanks, Stefan -Ursprüngliche Nachricht- Von: Jacques Kilchoer [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 13. November 2003 20:54 An: Multiple recipients of list ORACLE-L Betreff: RE: Looking for help - sql*loader and truncate In that case you could create a procedure owned by the data owner that does the truncate, grant execute on the procedure to the data loader, and use SQL*Plus to call the truncate procedure before the SQL*load starts. -Original Message- Stefan Jahnke .. and there is another scenario to use replace. As you mention it, that's what we do, too ;). The package owner (who owns the transformation packages) also does the load, so we use replace here, since I didn't feel like granting DROP ... to the package user or use the data owner to do the load. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Looking for help - sql*loader and truncate
In that case you could create a procedure owned by the data owner that does the truncate, grant execute on the procedure to the data loader, and use SQL*Plus to call the truncate procedure before the SQL*load starts. -Original Message- Stefan Jahnke .. and there is another scenario to use replace. As you mention it, that's what we do, too ;). The package owner (who owns the transformation packages) also does the load, so we use replace here, since I didn't feel like granting DROP ... to the package user or use the data owner to do the load. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Remote SQL*Loader session won't die
We are creating some Unix (DEC/Compaq/HP) ksh scripts to move data to a remote system and then execute SQL*Loader (Oracle 8.1.6). Logging in on the remote system, this works fine. If the commands are executed from the command line using rsh, it works fine. But when SQL*Loader is executed through rsh, within a script, the load works fine, but the process doesn't terminate, just hangs. To add another wrinkle, going to a different machine works fine also, but the system administrator says there isn't any difference between the machines. And executing anything other than SQL*Loader works fine. I'm stumped and would welcome any ideas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Remote SQL*Loader session won't die
Just a guess, but could your STDIN be redefined in the method that doesn't work and it's waiting for you to enter your username or something silly? Perhaps you could post the command(s) you're trying to use. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:10 PM To: Multiple recipients of list ORACLE-L Subject: Remote SQL*Loader session won't die We are creating some Unix (DEC/Compaq/HP) ksh scripts to move data to a remote system and then execute SQL*Loader (Oracle 8.1.6). Logging in on the remote system, this works fine. If the commands are executed from the command line using rsh, it works fine. But when SQL*Loader is executed through rsh, within a script, the load works fine, but the process doesn't terminate, just hangs. To add another wrinkle, going to a different machine works fine also, but the system administrator says there isn't any difference between the machines. And executing anything other than SQL*Loader works fine. I'm stumped and would welcome any ideas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Remote SQL*Loader session won't die
I have no idea why the same command would work differently on different machines if all other things are the same. Just a shot in the dark here: Sometimes rsh must be used with -n option (see man page.) Of course, you know using r commands is in hideous opposition to politically correct security and auditing requirements ... you know ... like the requirement that people to log in as CISTUM rather than SYSTEM. -Original Message- We are creating some Unix (DEC/Compaq/HP) ksh scripts to move data to a remote system and then execute SQL*Loader (Oracle 8.1.6). Logging in on the remote system, this works fine. If the commands are executed from the command line using rsh, it works fine. But when SQL*Loader is executed through rsh, within a script, the load works fine, but the process doesn't terminate, just hangs. To add another wrinkle, going to a different machine works fine also, but the system administrator says there isn't any difference between the machines. And executing anything other than SQL*Loader works fine. I'm stumped and would welcome any ideas. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Sql loader
Hallo, Anyone whom could tell me if it is in an sqlloader possible to write for instance this if you dont know the exactly name of file. infile '/d31/datafiler/sema/incoming/konkurrenter.*' If there are going to be files with different extensions fo rinstance the first time a file is going to be inserted will be konkurrenter.txt.1 and the next time the file will have the name konkurrenter.txt.2 etc. How should I handle this? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Sql loader
1) Write a perl script which will rewrite the control file on the fly. 2) Write a script which will generate a symbolic link for the file du jour. 3) Do PURGE *.txt/KEEP=1 (I couldn't resist, it was the best OS ever). -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 11, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Subject: Sql loader Hallo, Anyone whom could tell me if it is in an sqlloader possible to write for instance this if you dont know the exactly name of file. infile '/d31/datafiler/sema/incoming/konkurrenter.*' If there are going to be files with different extensions fo rinstance the first time a file is going to be inserted will be konkurrenter.txt.1 and the next time the file will have the name konkurrenter.txt.2 etc. How should I handle this? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: sql loader log file problem
Title: Message But wc -l OBCONT.LOAD.030909 gives 9488 . how will I find the value 8842 from the file. Thanks Manoj -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tanel PoderSent: Wednesday, September 10, 2003 11:19 AMTo: [EMAIL PROTECTED]Subject: Re: sql loader log file problem Hi! You probably have 8842separate rows in your input text file. But yoursqlloader knows how to extract several database records from one physical text file row, depending on your controlfile. Thus, in some cases it makes multiple records out of single row and here comes the difference. Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 10, 2003 8:24 AM Subject: sql loader log file problem Hi All, the sql loader log file is giving the o/p as : 8842 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Total logical records skipped: 0Total logical records read: 9488Total logical records rejected: 0Total logical records discarded: 0 But the records loaded in the table is 9488 instead of 8842. is this an error in log file o/p. can such problem be possible in sql loader. also this is the first time I am getting such different o/p when there are no rejected and discarded. can anyone explain this. Thanks Manoj This e-Mail may contain proprietary and confidential information and is sent for the intended recipient(s) only. If by an addressing or transmission error this mail has been misdirected to you, you are requested to delete this mail immediately. You are also hereby notified that any use, any form of reproduction, dissemination, copying, disclosure, modification, distribution and/or publication of this e-mail message, contents or its attachment other than by its intended recipient/s is strictly prohibited. Visit Us at http://www.polaris.co.in
sql loader log file problem
Title: Message Hi All, the sql loader log file is giving the o/p as : 8842 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Total logical records skipped: 0Total logical records read: 9488Total logical records rejected: 0Total logical records discarded: 0 But the records loaded in the table is 9488 instead of 8842. is this an error in log file o/p. can such problem be possible in sql loader. also this is the first time I am getting such different o/p when there are no rejected and discarded. can anyone explain this. Thanks Manoj This e-Mail may contain proprietary and confidential information and is sent for the intended recipient(s) only. If by an addressing or transmission error this mail has been misdirected to you, you are requested to delete this mail immediately. You are also hereby notified that any use, any form of reproduction, dissemination, copying, disclosure, modification, distribution and/or publication of this e-mail message, contents or its attachment other than by its intended recipient/s is strictly prohibited. Visit Us at http://www.polaris.co.in
Re: sql loader log file problem
Title: Message Hi! You probably have 8842separate rows in your input text file. But yoursqlloader knows how to extract several database records from one physical text file row, depending on your controlfile. Thus, in some cases it makes multiple records out of single row and here comes the difference. Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 10, 2003 8:24 AM Subject: sql loader log file problem Hi All, the sql loader log file is giving the o/p as : 8842 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Total logical records skipped: 0Total logical records read: 9488Total logical records rejected: 0Total logical records discarded: 0 But the records loaded in the table is 9488 instead of 8842. is this an error in log file o/p. can such problem be possible in sql loader. also this is the first time I am getting such different o/p when there are no rejected and discarded. can anyone explain this. Thanks Manoj
RE: RE: URGENT : sql*loader performance problem on partionned tab
Title: RE: RE: URGENT : sql*loader performance problem on partionned tab
here the trace :
SELECT STATEMENT, GOAL = CHOOSE 2 72 1368
FOR UPDATE
FILTER
PARTITION RANGE SINGLE
TABLE ACCESS BY LOCAL INDEX ROWID FICOM HREL_FUSION 2 72 1368
INDEX RANGE SCAN FICOM IDX_HREL_FUSION_P_COD_REL 1 72
TABLE ACCESS BY INDEX ROWID FICOM PRIMEDI_ENR2_TEMP_FUSION 2 1 32
INDEX RANGE SCAN FICOM IDX_PRIMCOD_ENR2_TEMP_FUSION 1 1
TIA
Philippe Nguyen
CETELEM - Administration, Architecture Décisionnelle
Direction Customer Relationship Management
E-Mail : [EMAIL PROTECTED]
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Envoyé : 05 September 2003 00:09
À : Multiple recipients of list ORACLE-L
Objet : RE: RE: URGENT : sql*loader performance problem on partionned
tab
Did you explain plan? I suspect FTS taking place in case of NOT EXISTS.
It must be using Range scan for the non partitioned table.
Can you confirm / post the explain plan.
GovindanK
Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli --- too much
indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging
part of the scritp in located here :
**
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**
The mostly amazing thing is that the same script work in 12 min on
non-partitionned table.
Here a subset of the script :
***
cat EOD $maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where
primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and
primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and
primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(50);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1
(curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) '
||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
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).
RE: RE: URGENT : sql*loader performance problem on partionned tab
Title: RE: RE: URGENT : sql*loader performance problem on partionned table - not sql*loader problem but cursor pb!
Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli --- too much indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging part of the scritp in located here :
**
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**
The mostly amazing thing is that the same script work in 12 min on non-partitionned table.
Here a subset of the script :
***
cat EOD $maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(50);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1 (curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) ' ||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***
RE: RE: URGENT : sql*loader performance problem on partionned tab
Did you explain plan? I suspect FTS taking place in case of NOT EXISTS.
It must be using Range scan for the non partitioned table.
Can you confirm / post the explain plan.
GovindanK
Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli --- too much
indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging
part of the scritp in located here :
**
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**
The mostly amazing thing is that the same script work in 12 min on
non-partitionned table.
Here a subset of the script :
***
cat EOD $maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where
primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and
primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and
primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(50);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1
(curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) '
||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
RE: URGENT : sql*loader performance problem on partionned table
Title: RE: URGENT : sql*loader performance problem on partionned table precision : Oracle 8.1.7.3 (64 bits) in Solaris 8 -Message d'origine- De : NGUYEN Philippe (Cetelem) Envoyé : 02 September 2003 18:14 À : '[EMAIL PROTECTED]' Objet : URGENT : sql*loader performance problem on partionned table Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance!
RE: URGENT : sql*loader performance problem on partionned table
Title: RE: URGENT : sql*loader performance problem on partionned table thank U Dennis, I use local index, the script is still running (2hours now! instead of 10-20 min) and here is the statement in question (the script who used non-partionned table is already ended) SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or AND primedi_enr2_temp_fusion.code_logis = hrel_fusion.cod_rel AND primedi_enr2_temp_fusion.date_logis = hrel_fusion.dat_rel) FOR UPDATE The explain plan show that hrel_fusion table is ACCESS BY LOCAL INDEX ROWID and primedi table used INDEX too. When I looking at lock tables it show me 2 session locked : SQL select session_id, oracle_username, object_name 2 from v$locked_object lo, dba_objects o 3 where lo.object_id = o.object_id 4 ; SESSION_ID ORACLE_USERNAME OBJECT_NAME -- -- 7 FICOM HREL_FUSION 7 FICOM HREL_FUSION -Why are there 2 locks on this table even there only one session ? - Yesterday I did gather_statitic on this table and it worked fine (12 min), should I do this each day ? TIA Philippe -Message d'origine- De : DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Envoyé : 02 September 2003 19:45 À : Multiple recipients of list ORACLE-L Objet : RE: URGENT : sql*loader performance problem on partionned table Philippe You aren't providing many details on which to base some guesses. However, your statement brand new disks implies that you are adding additional partitions to an existing table. Then, your statement should I drop indexes implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED]
Re: URGENT : sql*loader performance problem on partionned table
Yesterday i posted a reply on this .. but did not reach. Check if too much logging taking place. Avoid this with loading as UNRECOVERABLE; Or else Presort the data on the index key to minimise the use of Temp segment. As of now i am able to think of only these two. HTH GovindanK Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: URGENT : sql*loader performance problem on partionned table
Hello Did you check alert.log for any unusual messages? May be it is using lot of rollback / archiving. You can use unrecoverable option to load. You have not mentioned whether you are using direct load or not. Check if too many extents are getting allocated at runtime. That is going to slow down the load. Presorting data on indexed columns is another method to speeden up load. This is likely to minimise the use of temp segment. HTH GovindanK Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: URGENT : sql*loader performance problem on partionned table
Title: RE: URGENT : sql*loader performance problem on partionned table thankx for all those advbices, actually,the problem does not come from the sql*loader but from this particular statement: SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or AND primedi_enr2_temp_fusion.code_logis = hrel_fusion.cod_rel AND primedi_enr2_temp_fusion.date_logis = hrel_fusion.dat_rel) FOR UPDATE The explain plan show that hrel_fusion table is ACCESS BY LOCAL INDEX ROWID and primedi table used INDEX too. When I looking at lock tables it show me 2 session locked : SQL select session_id, oracle_username, object_name 2 from v$locked_object lo, dba_objects o 3 where lo.object_id = o.object_id 4 ; SESSION_ID ORACLE_USERNAME OBJECT_NAME -- -- 7 FICOM HREL_FUSION 7 FICOM HREL_FUSION -Why are there 2 locks on this table even there only one session ? - Yesterday I did gather_statitic on this table and it worked fine (12 min), should I do this each day ? TIA Philippe -Message d'origine- De : DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Envoyé : 02 September 2003 19:45 À : Multiple recipients of list ORACLE-L Objet : RE: URGENT : sql*loader performance problem on partionned table Philippe You aren't providing many details on which to base some guesses. However, your statement "brand new disks" implies that you are adding additional partitions to an existing table. Then, your statement "should I drop indexes" implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED]
Re: RE: URGENT : sql*loader performance problem on partionned table
1. by 'filling' are you doing inserts also? I see a 'FOR UPDATE' statement which implies that you are doing updates from with in a cursor. 2. How many indexes do you have on this table? 3. Is anything else running at the same time? 4. Are you doing your DML from with in a cursor. This is very slow. Try to do it all in SQL. 5. Consider changing your not exists to Where NOT IN (SELECT /*+ hash_aj */ ...) That can be faster if your sub-query is significantly less costly than your your outer query(its the opposite for exists). Wont necessarily help in your case. You can also eliminate the join with the 'in'. This can improve performance as well. Please post a subset of your batch script. Please format it so its readable. Ill look at it. Also please post how many rows are in each table. Ill see if I can find anything. I think the two locks on the same object are locks on seperate partitions. Not sure. From: NGUYEN Philippe (Cetelem) [EMAIL PROTECTED] Date: 2003/09/03 Wed AM 09:59:27 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: URGENT : sql*loader performance problem on partionned table thank U Dennis, I use local index, the script is still running (2hours now! instead of 10-20 min) and here is the statement in question (the script who used non-partionned table is already ended) SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or AND primedi_enr2_temp_fusion.code_logis = hrel_fusion.cod_rel AND primedi_enr2_temp_fusion.date_logis = hrel_fusion.dat_rel) FOR UPDATE The explain plan show that hrel_fusion table is ACCESS BY LOCAL INDEX ROWID and primedi table used INDEX too. When I looking at lock tables it show me 2 session locked : SQL select session_id, oracle_username, object_name 2 from v$locked_object lo, dba_objects o 3 where lo.object_id = o.object_id 4 ; SESSION_ID ORACLE_USERNAMEOBJECT_NAME -- -- 7 FICOM HREL_FUSION 7 FICOM HREL_FUSION -Why are there 2 locks on this table even there only one session ? - Yesterday I did gather_statitic on this table and it worked fine (12 min), should I do this each day ? TIA Philippe -Message d'origine- De : DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Envoyé : 02 September 2003 19:45 À : Multiple recipients of list ORACLE-L Objet : RE: URGENT : sql*loader performance problem on partionned table Philippe You aren't providing many details on which to base some guesses. However, your statement brand new disks implies that you are adding additional partitions to an existing table. Then, your statement should I drop indexes implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Title: RE: URGENT : sql*loader performance problem on partionned table thank U Dennis, I use local index, the script is still running (2hours now! instead of 10-20 min) and here is the statement in question (the script who used non-partionned table is already ended) SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or
URGENT : sql*loader performance problem on partionned table
Title: URGENT : sql*loader performance problem on partionned table Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance!
RE: URGENT : sql*loader performance problem on partionned table
Philippe You aren't providing many details on which to base some guesses. However, your statement brand new disks implies that you are adding additional partitions to an existing table. Then, your statement should I drop indexes implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL Loader help
I know that there are several sql loader gurus on this list. I'm having a problem loading data from a third party vendor. into what is basically a LONG column The file is ASCII, tab delimited. The specs on the input data say that this particular column is a memo field (the originating database is FoxPro.. joy) I'm trying to load this into a 9.2.0.2 database. And it's barfing. This is annoying me no end and so I'm asking for help. I don't have Jonathan's book here with me! (that would, of course, be easy) Rachel __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader help
Rachel, I do not claim to be a guru, but I do happen to have Jonathan's book here. The first thing is what field type are you using for this memo field? The book says you should use CHAR for VARCHAR2, CHAR, LONG and other related. What error are you getting? Can you post the lines from the ctl? Is there a chance there are TAB's in the data? Stephen [EMAIL PROTECTED] 08/29/03 09:24AM I know that there are several sql loader gurus on this list. I'm having a problem loading data from a third party vendor. into what is basically a LONG column The file is ASCII, tab delimited. The specs on the input data say that this particular column is a memo field (the originating database is FoxPro.. joy) I'm trying to load this into a 9.2.0.2 database. And it's barfing. This is annoying me no end and so I'm asking for help. I don't have Jonathan's book here with me! (that would, of course, be easy) Rachel __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader help
Stephen, I was using CHAR for the LONG column the error is actually on the field in the file FOLLOWING the LONG column... I was getting a message that the data was too large. So I did the following and it seems to be working, although I can't get it to work via direct path this way: I changed the column in the Oracle table to CLOB I changed the definition of the column in the ctl file to char(1) The column separator was X'001' and the end of record marker was X'002'. I had been translating them both, the 001 to a tab and the 002 to a newline. I translate only the end of record marker now and define the column separator as X'1' in the ctl file. So far, it's working. Of course, it's slow as molasses, and this is a column which we DON'T NEED but then again... it's the Friday before a long weekend. I just want to get it to work, I'll make it pretty and fast later Rachel --- Stephen Andert [EMAIL PROTECTED] wrote: Rachel, I do not claim to be a guru, but I do happen to have Jonathan's book here. The first thing is what field type are you using for this memo field? The book says you should use CHAR for VARCHAR2, CHAR, LONG and other related. What error are you getting? Can you post the lines from the ctl? Is there a chance there are TAB's in the data? Stephen [EMAIL PROTECTED] 08/29/03 09:24AM I know that there are several sql loader gurus on this list. I'm having a problem loading data from a third party vendor. into what is basically a LONG column The file is ASCII, tab delimited. The specs on the input data say that this particular column is a memo field (the originating database is FoxPro.. joy) I'm trying to load this into a 9.2.0.2 database. And it's barfing. This is annoying me no end and so I'm asking for help. I don't have Jonathan's book here with me! (that would, of course, be easy) Rachel __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: info on SQL Loader?
For a few quick hints, try http://www.orafaq.com/faqloadr.htm The O'Reilly book recommended by Dennis and Kenneth is good, but I reach for the FAQ page first. Craig Healey -Original Message- From: Maryann Atkinson [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 18:09 To: Multiple recipients of list ORACLE-L Subject: info on SQL Loader? Does anyone happen to know of any links/info etc on the SQL loader starting from the basics? thanks, maa load data replace preserve blanks into table working_asc ( TRANposition(1:1)char , SPECIALTY_CODE position(3:7)char , SUFFIX position(8:8)char , DESCRIPTION position(9:28) char , EXPIRED_IND constant N ) ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: info on SQL Loader?
For a few quick hints, try http://www.orafaq.com/faqloadr.htm The O'Reilly book recommended by Dennis and Kenneth is good, but I reach for the FAQ page first. Craig Healey -Original Message- From: Maryann Atkinson [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 18:09 To: Multiple recipients of list ORACLE-L Subject: info on SQL Loader? Does anyone happen to know of any links/info etc on the SQL loader starting from the basics? thanks, maa load data replace preserve blanks into table working_asc ( TRANposition(1:1)char , SPECIALTY_CODE position(3:7)char , SUFFIX position(8:8)char , DESCRIPTION position(9:28) char , EXPIRED_IND constant N ) ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
info on SQL Loader?
Does anyone happen to know of any links/info etc on the SQL loader starting from the basics? thanks, maa load data replace preserve blanks into table working_asc ( TRANposition(1:1)char , SPECIALTY_CODE position(3:7)char , SUFFIX position(8:8)char , DESCRIPTION position(9:28) char , EXPIRED_IND constant N ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: info on SQL Loader?
Maryann If you just need to know a little to get by, go to http://metalink.oracle.com, click on documentation and locate the Utilities manual. If you think you will be using SQL*Loader quite a bit, go ahead and purchase Oracle SQL*Loader The Definitive Guide by Jonathan Gennick and Sanjay Mishra. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 19, 2003 12:09 PM To: Multiple recipients of list ORACLE-L Does anyone happen to know of any links/info etc on the SQL loader starting from the basics? thanks, maa load data replace preserve blanks into table working_asc ( TRANposition(1:1)char , SPECIALTY_CODE position(3:7)char , SUFFIX position(8:8)char , DESCRIPTION position(9:28) char , EXPIRED_IND constant N ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: info on SQL Loader?
O'Reilly has a good book that I have used. Oracle SQL*Loader, The Definitive Guide by Gennick Mishra. Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 12:09 PM Does anyone happen to know of any links/info etc on the SQL loader starting from the basics? thanks, maa load data replace preserve blanks into table working_asc ( TRANposition(1:1)char , SPECIALTY_CODE position(3:7)char , SUFFIX position(8:8)char , DESCRIPTION position(9:28) char , EXPIRED_IND constant N ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: KENNETH JANUSZ INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL*Loader - append to a table while checking column against another table
If you load your data into a table with an enable foreign key, it will be checked on the fly by SQL*Loader with the conventional path. In direct mode it disables constraints, and I am unsure whether it checks them when reenabling them (something which you can optionally get); any way it would let you with the manual check to do. If your CSV file is not in the million of rows or more range, I would go for SQL*Loader with the conventional path. HTH SF - --- Original Message --- - From: Saira Somani-Mendelin [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 14 Aug 2003 05:44:23 List, I have been given a csv file to load into an Oracle table. This table has no referential integrity constraints (it is checked via the application) so I could end up inserting a bogus record. Is there any way for me to check for the existence of a record in another table while loading data using SQL*Loader? Here's the process I use right now: Load records into a temporary table. Query these records against the reference table. If record doesn't exist, spool it into a file, give it back to the BA, get them to fix it, then go through the whole process again until there are no bad records. Seems like a roundabout way to do things - it definitely works for me but I just wonder if there is an easier way. Thanks in advance, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: How to take sql*loader trace
Create or replace database trigger turn_tracing_on after logon on schema.scott Begin Execute immediate 'alter session set events=''10046 trace name context forever, level 8'''; End; / That should give you more then enough info to trace, especially on 9i. -- Mladen Gogala Oracle DBA -Original Message- Senthil Kumar Sent: Monday, August 11, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL*Loader - append to a table while checking column against another table
Another nifty trick is to use external tables. Believe it or not, you can even specify /*+ parallel */ in those babies. -- Mladen Gogala Oracle DBA -Original Message- Stephane Faroult Sent: Thursday, August 14, 2003 9:59 AM To: Multiple recipients of list ORACLE-L another table If you load your data into a table with an enable foreign key, it will be checked on the fly by SQL*Loader with the conventional path. In direct mode it disables constraints, and I am unsure whether it checks them when reenabling them (something which you can optionally get); any way it would let you with the manual check to do. If your CSV file is not in the million of rows or more range, I would go for SQL*Loader with the conventional path. HTH SF - --- Original Message --- - From: Saira Somani-Mendelin [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 14 Aug 2003 05:44:23 List, I have been given a csv file to load into an Oracle table. This table has no referential integrity constraints (it is checked via the application) so I could end up inserting a bogus record. Is there any way for me to check for the existence of a record in another table while loading data using SQL*Loader? Here's the process I use right now: Load records into a temporary table. Query these records against the reference table. If record doesn't exist, spool it into a file, give it back to the BA, get them to fix it, then go through the whole process again until there are no bad records. Seems like a roundabout way to do things - it definitely works for me but I just wonder if there is an easier way. Thanks in advance, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: How to take sql*loader trace/ AFTER LOGON SCHEMA TRIGGER
Hi! I think administer database trigger privilege isn't an issue here, it's not needed when creating schema level logon trigger. Try to run your execute immediate command first from command line under your user: exec Execute immediate 'alter session set events=''10046 trace name context forever, level 8'''; If it returnig insufficient privileges error, then you still don't have alter session privilege granted (directly). Check select * from session_privs then. Note, that be careful with logon triggers, you should have exception handling written into those in case something goes wrong. Otherwise no normal user can log on. The users with ADMINISTER DATABASE TRIGGER privilege can always log on, but the error is ignored silently (error is written in udump), thus normal users defintitely shoult not have this priv. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 2:34 PM Try with ADMINISTER DATABASE TRIGGER On 2003.08.13 04:29, Jack van Zanen wrote: Yes I tried granting alter session first -Original Message- Sent: Tuesday, August 12, 2003 11:24 PM To: Multiple recipients of list ORACLE-L Does the user whom you created the trigger have alter session privilege? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 4:39 PM As system (8.1.7.3): create or replace trigger TRIGGER_NAME after logon on SCHEMA.schema begin Execute immediate 'alter session set events=''10046 trace name context forever, level 12'''; end; When I logon after creating this trigger as the user SCHEMA I get the error message insufficient privileges. If I grant DBA it seems to work but that is a bit much for privileges. Which privileges are neccesary to run this?? Just alter session won't work. Jack -Original Message- Sent: Monday, August 11, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Create or replace database trigger turn_tracing_on after logon on schema.scott Begin Execute immediate 'alter session set events=''10046 trace name context forever, level 8'''; End; / That should give you more then enough info to trace, especially on 9i. -- Mladen Gogala Oracle DBA -Original Message- Senthil Kumar Sent: Monday, August 11, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
Re: How to take sql*loader trace/ AFTER LOGON SCHEMA TRIGGER
Does the user whom you created the trigger have alter session privilege? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 4:39 PM As system (8.1.7.3): create or replace trigger TRIGGER_NAME after logon on SCHEMA.schema begin Execute immediate 'alter session set events=''10046 trace name context forever, level 12'''; end; When I logon after creating this trigger as the user SCHEMA I get the error message insufficient privileges. If I grant DBA it seems to work but that is a bit much for privileges. Which privileges are neccesary to run this?? Just alter session won't work. Jack -Original Message- Sent: Monday, August 11, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Create or replace database trigger turn_tracing_on after logon on schema.scott Begin Execute immediate 'alter session set events=''10046 trace name context forever, level 8'''; End; / That should give you more then enough info to trace, especially on 9i. -- Mladen Gogala Oracle DBA -Original Message- Senthil Kumar Sent: Monday, August 11, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: How to take sql*loader trace/ AFTER LOGON SCHEMA TRIGGER
Yes I tried granting alter session first -Original Message- Sent: Tuesday, August 12, 2003 11:24 PM To: Multiple recipients of list ORACLE-L Does the user whom you created the trigger have alter session privilege? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 4:39 PM As system (8.1.7.3): create or replace trigger TRIGGER_NAME after logon on SCHEMA.schema begin Execute immediate 'alter session set events=''10046 trace name context forever, level 12'''; end; When I logon after creating this trigger as the user SCHEMA I get the error message insufficient privileges. If I grant DBA it seems to work but that is a bit much for privileges. Which privileges are neccesary to run this?? Just alter session won't work. Jack -Original Message- Sent: Monday, August 11, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Create or replace database trigger turn_tracing_on after logon on schema.scott Begin Execute immediate 'alter session set events=''10046 trace name context forever, level 8'''; End; / That should give you more then enough info to trace, especially on 9i. -- Mladen Gogala Oracle DBA -Original Message- Senthil Kumar Sent: Monday, August 11, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tanel Poder INET: [EMAIL
RE: How to take sql*loader trace/ AFTER LOGON SCHEMA TRIGGER
As system (8.1.7.3): create or replace trigger TRIGGER_NAME after logon on SCHEMA.schema begin Execute immediate 'alter session set events=''10046 trace name context forever, level 12'''; end; When I logon after creating this trigger as the user SCHEMA I get the error message insufficient privileges. If I grant DBA it seems to work but that is a bit much for privileges. Which privileges are neccesary to run this?? Just alter session won't work. Jack -Original Message- Sent: Monday, August 11, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Create or replace database trigger turn_tracing_on after logon on schema.scott Begin Execute immediate 'alter session set events=''10046 trace name context forever, level 8'''; End; / That should give you more then enough info to trace, especially on 9i. -- Mladen Gogala Oracle DBA -Original Message- Senthil Kumar Sent: Monday, August 11, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: How to take sql*loader trace
Senthil Are you using direct-path loader? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL*Loader - append to a table while checking column against another table
List, I have been given a csv file to load into an Oracle table. This table has no referential integrity constraints (it is checked via the application) so I could end up inserting a bogus record. Is there any way for me to check for the existence of a record in another table while loading data using SQL*Loader? Here's the process I use right now: Load records into a temporary table. Query these records against the reference table. If record doesn't exist, spool it into a file, give it back to the BA, get them to fix it, then go through the whole process again until there are no bad records. Seems like a roundabout way to do things - it definitely works for me but I just wonder if there is an easier way. Thanks in advance, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: How to take sql*loader trace/ AFTER LOGON SCHEMA TRIGGER
Try with ADMINISTER DATABASE TRIGGER On 2003.08.13 04:29, Jack van Zanen wrote: Yes I tried granting alter session first -Original Message- Sent: Tuesday, August 12, 2003 11:24 PM To: Multiple recipients of list ORACLE-L Does the user whom you created the trigger have alter session privilege? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 4:39 PM As system (8.1.7.3): create or replace trigger TRIGGER_NAME after logon on SCHEMA.schema begin Execute immediate 'alter session set events=''10046 trace name context forever, level 12'''; end; When I logon after creating this trigger as the user SCHEMA I get the error message insufficient privileges. If I grant DBA it seems to work but that is a bit much for privileges. Which privileges are neccesary to run this?? Just alter session won't work. Jack -Original Message- Sent: Monday, August 11, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Create or replace database trigger turn_tracing_on after logon on schema.scott Begin Execute immediate 'alter session set events=''10046 trace name context forever, level 8'''; End; / That should give you more then enough info to trace, especially on 9i. -- Mladen Gogala Oracle DBA -Original Message- Senthil Kumar Sent: Monday, August 11, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Re: How to take sql*loader trace
Issue this command from SQL*Plus prior to starting your SQL*Loader session: ALTER SYSTEM SET max_dump_file_size = unlimited; ALTER SYSTEM SET timed_statistics = true; ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12'; Then, issue this command after the session has started: ALTER SYSTEM SET EVENTS '10046 trace name context off'; This will trace all NEW sessions that start between the two SET EVENTS commands. Jay [EMAIL PROTECTED] 08/11/03 10:39AM Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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) **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: How to take sql*loader trace
That is why my advice was 10046 trace name context forever, level 8, not SQL_TRACE. With 10046, level 8 you can still get waits (tkprof in 9i understands them). You cannot Get explain plan because direct path doesn't use SQL to insert into the database. -- Mladen Gogala Oracle DBA -Original Message- DENNIS WILLIAMS Sent: Monday, August 11, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Senthil Are you using direct-path loader? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: How to take sql*loader trace
Mladen Yes, when I saw your posting, I assumed that was what you were getting at. I'll have to remember this one if I have trouble with a SQL*Loader session. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 10:34 AM To: Multiple recipients of list ORACLE-L That is why my advice was 10046 trace name context forever, level 8, not SQL_TRACE. With 10046, level 8 you can still get waits (tkprof in 9i understands them). You cannot Get explain plan because direct path doesn't use SQL to insert into the database. -- Mladen Gogala Oracle DBA -Original Message- DENNIS WILLIAMS Sent: Monday, August 11, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Senthil Are you using direct-path loader? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
How to take sql*loader trace
Hi All, I'm loading a set of data into one of my schema. And wanted to take the trace files also. I tried the follwoing. 1. Identified the sid,serila# for the sql*loader session. 2. used the follwoing, exec sys.dbms_system.set_sql_trace_in_session(19,11250,TRUE); but this is not generation any trace files. But if I trun my system to sql_trace=TRUE, I'm able to collect the trace details. I don't want to do this. I want to take the trace only for the sql*loader session. How do i do that. TIA, Senthil. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader problem
Tim, Sorry for late reply. I was out for a few days. You are right. CHAR(4000) fixed the problem. I really appreciate your help and all the help from other people who answered my question. Thanks. Anna From: Tim Gorman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: SQL Loader problem Date: Tue, 29 Jul 2003 15:09:29 -0800 Sliced bread or not, the syntax problem will be the same since external tables are modeled on SQL*Loader syntax... :-) Anna, I suspect that there is confusion on the SQL*Loader concepts of external datatypes and internal datatypes, which incidentally is shared by external tables currently. SQL*Loader's internal datatypes match the standard Oracle RDBMS datatypes, but the external datatypes are something else altgoether... I don't have docs available, but specifying an external datatype of VARCHAR or VARCHAR2 probably doesn't mean what you want it to mean. If memory serves, I think it is supposed to be a two-byte binary integer (which is the data length) to be followed by the data itself. Or something like that... Try CHAR(4000), perhaps? The Server Utilities manual from the standard Oracle doc-set on http://otn.oracle.com; has all this stuff in it... Hope this helps... -Tim on 7/29/03 3:19 PM, Mladen Gogala at [EMAIL PROTECTED] wrote: Try with external tables. The best thing since sliced bread. On 2003.07.29 17:39, Anna Li wrote: Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is 4000 characters, but 1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL Loader problem
Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is 4000 characters, but 1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader problem
Try with external tables. The best thing since sliced bread. On 2003.07.29 17:39, Anna Li wrote: Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is 4000 characters, but 1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader problem
Sliced bread or not, the syntax problem will be the same since external tables are modeled on SQL*Loader syntax... :-) Anna, I suspect that there is confusion on the SQL*Loader concepts of external datatypes and internal datatypes, which incidentally is shared by external tables currently. SQL*Loader's internal datatypes match the standard Oracle RDBMS datatypes, but the external datatypes are something else altgoether... I don't have docs available, but specifying an external datatype of VARCHAR or VARCHAR2 probably doesn't mean what you want it to mean. If memory serves, I think it is supposed to be a two-byte binary integer (which is the data length) to be followed by the data itself. Or something like that... Try CHAR(4000), perhaps? The Server Utilities manual from the standard Oracle doc-set on http://otn.oracle.com; has all this stuff in it... Hope this helps... -Tim on 7/29/03 3:19 PM, Mladen Gogala at [EMAIL PROTECTED] wrote: Try with external tables. The best thing since sliced bread. On 2003.07.29 17:39, Anna Li wrote: Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is 4000 characters, but 1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL*Loader problem - constraint violation
After reading some archives on google, I came up with this solution: Drop the index. Use sqlldr to append to the table - for the id column, use: (lc_rid sequence (max,1) ...) Recreate the index. However, when I did this, I received an error message saying that I can't have duplicates in the index (of course not because the index is to be unique). But I checked lc_rid for duplicates and didn't find any. So what am I missing here? Thanks, Saira -Original Message- Saira Somani-Mendelin Sent: July 7, 2003 3:59 PM To: Multiple recipients of list ORACLE-L I'm new to sqlldr and here is my control file: LOAD DATA INFILE '/home/oracle/116.csv' BADFILE '/home/oracle/116.bad' DISCARDFILE '/home/oracle/116.rej' APPEND INTO TABLE LC_F FIELDS TERMINATED BY ',' TRAILING NULLCOLS (lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt, cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty ,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp) lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow? Here is a snippet from the log file: Record 1: Rejected - Error on table LC_F. ORA-1: unique constraint (TEST65W.LCI_LOC) violated LCI_LOC looks like this: CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) If anyone can help, it would be very appreciated. Thank you, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL*Loader problem - constraint violation
Saira It looks like your index is being created on the LOC column, right? CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) So your change to the lc_rid column did not fix this problem. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 08, 2003 9:29 AM To: Multiple recipients of list ORACLE-L After reading some archives on google, I came up with this solution: Drop the index. Use sqlldr to append to the table - for the id column, use: (lc_rid sequence (max,1) ...) Recreate the index. However, when I did this, I received an error message saying that I can't have duplicates in the index (of course not because the index is to be unique). But I checked lc_rid for duplicates and didn't find any. So what am I missing here? Thanks, Saira -Original Message- Saira Somani-Mendelin Sent: July 7, 2003 3:59 PM To: Multiple recipients of list ORACLE-L I'm new to sqlldr and here is my control file: LOAD DATA INFILE '/home/oracle/116.csv' BADFILE '/home/oracle/116.bad' DISCARDFILE '/home/oracle/116.rej' APPEND INTO TABLE LC_F FIELDS TERMINATED BY ',' TRAILING NULLCOLS (lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt, cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty ,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp) lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow? Here is a snippet from the log file: Record 1: Rejected - Error on table LC_F. ORA-1: unique constraint (TEST65W.LCI_LOC) violated LCI_LOC looks like this: CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) If anyone can help, it would be very appreciated. Thank you, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL*Loader problem - constraint violation
Ok. I must be legally blind :) Can this happen to anyone or just me? I will try this again with my bifocals on. Thank you for pointing it out kindly!! Saira -Original Message- Sent: July 8, 2003 8:46 AM To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Saira It looks like your index is being created on the LOC column, right? CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) So your change to the lc_rid column did not fix this problem. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 08, 2003 9:29 AM To: Multiple recipients of list ORACLE-L After reading some archives on google, I came up with this solution: Drop the index. Use sqlldr to append to the table - for the id column, use: (lc_rid sequence (max,1) ...) Recreate the index. However, when I did this, I received an error message saying that I can't have duplicates in the index (of course not because the index is to be unique). But I checked lc_rid for duplicates and didn't find any. So what am I missing here? Thanks, Saira -Original Message- Saira Somani-Mendelin Sent: July 7, 2003 3:59 PM To: Multiple recipients of list ORACLE-L I'm new to sqlldr and here is my control file: LOAD DATA INFILE '/home/oracle/116.csv' BADFILE '/home/oracle/116.bad' DISCARDFILE '/home/oracle/116.rej' APPEND INTO TABLE LC_F FIELDS TERMINATED BY ',' TRAILING NULLCOLS (lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt, cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty ,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp) lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow? Here is a snippet from the log file: Record 1: Rejected - Error on table LC_F. ORA-1: unique constraint (TEST65W.LCI_LOC) violated LCI_LOC looks like this: CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) If anyone can help, it would be very appreciated. Thank you, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Shared Pool Wait while using SQL Loader
Gurus , We run a Oracle 8.1.7.4.0 dw on Sun Solaris 2.8 version and its a data warehouse environment. There are lots of SQL Loader jobs which run on a day-to-day basis. Normally it takes 15-20 mins to finish the loading but today it is taking more than an hour and nothing seems to happen. This is a range partitioned table. Here are the results of the queries on v$ views. SID SEQ# EVENT -- -- P1TEXT P2TEXT P3TEXTWAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 1 26767 pmon timer duration 0 4750 WAITING 113 9126 db file sequential read file#block# blocks -1 0 WAITED SHORT TIME 86 2404 db file scattered read file#block# blocks2 0 WAITED KNOWN TIME 8 7539 smon timer sleep time failed 0 232 WAITING 70 36 SQL*Net message to client driver id#bytes -1 0 WAITED SHORT TIMEhandle address 3830864540 E4565A9C lock address 4121106472 F5A31828 10*mode+namespace31 001F 0 677 WAITING 70 32 SQL*Net message to client driver id1650815232 62657100 #bytes1 0001 0 00 -1 0 WAITED SHORT TIME Please advise ... - Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chindarkar, Chetan (CONS FIN , Contractor) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Shared Pool Wait while using SQL Loader
You queried everything from v$session_wait and you needed to query only the information relevant to the SID that is waiting. Other then that, the only session in WAITING status is waiting for a lock. Your chase is about to continue. Now that you have lock address, go on your way to v$lock table, grasshopper. The legend continues. On 2003.07.08 21:29, Chindarkar, Chetan (CONS FIN , Contractor) wrote: Gurus , We run a Oracle 8.1.7.4.0 dw on Sun Solaris 2.8 version and its a data warehouse environment. There are lots of SQL Loader jobs which run on a day-to-day basis. Normally it takes 15-20 mins to finish the loading but today it is taking more than an hour and nothing seems to happen. This is a range partitioned table. Here are the results of the queries on v$ views. SID SEQ# EVENT -- -- P1TEXT P2TEXT P3TEXTWAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 1 26767 pmon timer duration 0 4750 WAITING 113 9126 db file sequential read file#block# blocks -1 0 WAITED SHORT TIME 86 2404 db file scattered read file#block# blocks2 0 WAITED KNOWN TIME 8 7539 smon timer sleep time failed 0 232 WAITING 70 36 SQL*Net message to client driver id#bytes -1 0 WAITED SHORT TIMEhandle address 3830864540 E4565A9C lock address 4121106472 F5A31828 10*mode+namespace31 001F 0 677 WAITING 70 32 SQL*Net message to client driver id1650815232 62657100 #bytes1 0001 0 00 -1 0 WAITED SHORT TIME Please advise ... - Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chindarkar, Chetan (CONS FIN , Contractor) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Shared Pool Wait while using SQL Loader
Here is the information for relevant SID. SID SEQ# EVENT -- -- P1TEXT P1 P1RAW -- P2TEXT P2 P2RAW -- P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE -- -- --- --- 156 43997 library cache lock handle address 3830864540 E4565A9C lock address 4121103412 F5A30C34 10*mode+namespace31 001F 0 101 WAITING And here is the info from v$lock : SELECT * FROM v$lock WHERE sid = 156 ; ADDR KADDR SID TYID1ID2 LMODEREQUEST CTIME BLOCK -- -- -- -- -- -- -- -- 01A83FD4 01A840A0156 TX 65635 213866 6 0 5011 0 F58941AC F58941C0156 TM1548379 0 3 0 5005 0 F5893B94 F5893BA8156 TM 2832 0 3 0 5011 0 F58939B4 F58939C8156 TM 870072 0 3 0 5005 0 F58938C4 F58938D8156 TM1548381 0 3 0 4495 0 F58913BC F58913D0156 TM1548382 0 3 0 4170 0 F5891074 F5891088156 TM1548380 0 3 0 4715 0 7 rows selected. And here is the info from v$locked_object SELECT b.name, a.* FROM v$locked_object a, sys.obj$ b WHERE a.object_id = b.obj# and session_id = 156 ; NAME XIDUSNXIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME -- -- -- -- -- -- -- OS_USER_NAME PROCESS LOCKED_MODE -- - --- COMMENTS1 99 2138661548380156 CDW collprod 85233 COMMENTS1 99 213866 870072156 CDW collprod 85233 COMMENTS1 99 2138661548379156 CDW collprod 85233 COMMENTS1 99 2138661548382156 CDW collprod 85233 COMMENTS1 99 2138661548381156 CDW collprod 85233 PRODUCTION_LOG_DTL 1 99 213866 2832156 CDW collprod 85233 6 rows selected. Please advise .. Thanks - Chetan -Original Message- Sent: Tuesday, July 08, 2003 9:49 PM To: Multiple recipients of list ORACLE-L You queried everything from v$session_wait and you needed to query only the information relevant to the SID that is waiting. Other then that, the only session in WAITING status is waiting for a lock. Your chase is about to continue. Now that you have lock address, go on your way to v$lock table, grasshopper. The legend continues. On 2003.07.08 21:29, Chindarkar, Chetan (CONS FIN , Contractor) wrote: Gurus , We run a Oracle 8.1.7.4.0 dw on Sun Solaris 2.8 version and its a data warehouse environment. There are lots of SQL Loader jobs which run on a day-to-day basis. Normally it takes 15-20 mins to finish the loading but today it is taking more than an hour and nothing seems to happen. This is a range partitioned table. Here are the results of the queries on v$ views. SID SEQ# EVENT -- -- P1TEXT P2TEXT P3TEXTWAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 1 26767 pmon timer duration
Re: Shared Pool Wait while using SQL Loader
-- -- -- -- -- -- -- OS_USER_NAME PROCESS LOCKED_MODE -- - --- COMMENTS1 99 2138661548380 156 CDW collprod 85233 COMMENTS1 99 213866 870072 156 CDW collprod 85233 COMMENTS1 99 2138661548379 156 CDW collprod 85233 COMMENTS1 99 2138661548382 156 CDW collprod 85233 COMMENTS1 99 2138661548381 156 CDW collprod 85233 PRODUCTION_LOG_DTL 1 99 213866 2832 156 CDW collprod 85233 6 rows selected. Please advise .. Thanks - Chetan -Original Message- Sent: Tuesday, July 08, 2003 9:49 PM To: Multiple recipients of list ORACLE-L You queried everything from v$session_wait and you needed to query only the information relevant to the SID that is waiting. Other then that, the only session in WAITING status is waiting for a lock. Your chase is about to continue. Now that you have lock address, go on your way to v$lock table, grasshopper. The legend continues. On 2003.07.08 21:29, Chindarkar, Chetan (CONS FIN , Contractor) wrote: Gurus , We run a Oracle 8.1.7.4.0 dw on Sun Solaris 2.8 version and its a data warehouse environment. There are lots of SQL Loader jobs which run on a day-to-day basis. Normally it takes 15-20 mins to finish the loading but today it is taking more than an hour and nothing seems to happen. This is a range partitioned table. Here are the results of the queries on v$ views. SID SEQ# EVENT -- -- P1TEXT P2TEXT P3TEXTWAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 1 26767 pmon timer duration 0 4750 WAITING 113 9126 db file sequential read file#block# blocks - 1 0 WAITED SHORT TIME 86 2404 db file scattered read file#block# blocks2 0 WAITED KNOWN TIME 8 7539 smon timer sleep time failed 0 232 WAITING 70 36 SQL*Net message to client driver id#bytes - 1 0 WAITED SHORT TIMEhandle address 3830864540 E4565A9C lock address 4121106472 F5A31828 10*mode+namespace31 001F 0 677 WAITING 70 32 SQL*Net message to client driver id1650815232 62657100 #bytes1 0001 0 00 -1 0 WAITED SHORT TIME Please advise ... - Chetan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chindarkar, Chetan (CONS FIN , Contractor) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED
RE: Shared Pool Wait while using SQL Loader
Thanks for the script Mladen. I think I figured out what is the problem. There was a long running query which was creating a materialized view which had that table in the where clause. So the update and the SQL Loader jobs were hanging. - Chetan -Original Message- Sent: Wednesday, July 09, 2003 1:09 AM To: Multiple recipients of list ORACLE-L Oh, it's a library cache lock. Here is the script, contributed about 2 days ago by Andy Rivenes: -- FILE: libcache_lock.sql -- -- AUTHOR: Andy Rivenes -- -- DATE: 01/22/2003 -- -- DESCRIPTION: -- Query to display library cache lock/pin blockers and waiters -- Source: Note: 122793.1, HOW TO FIND THE SESSION HOLDING -- A LIBRARY CACHE LOCK -- -- The address of the object should allow access through -- v$open_cursor, v$sql views. -- -- REQUIREMENTS: -- Access to x$ tables (connect as sys or sysdba). -- -- MODIFICATIONS: -- -- SET LINESIZE 132; SET PAGESIZE 60; SET TRIMSPOOL off; -- COLUMN sid HEADING 'SID' FORMAT ; COLUMN objtyp HEADING 'Object|Type' FORMAT A25; COLUMN lktyp HEADING 'Lock|Type' FORMAT A4; COLUMN lkmod HEADING 'Mode|Held' FORMAT A10; COLUMN lkreq HEADING 'Mode|Request'FORMAT A10; COLUMN objaddr HEADING 'Address' FORMAT A10; COLUMN objdef HEADING 'Object' FORMAT A30 WORD_WRAPPED; -- BREAK ON lk.kgllkhdl SKIP 1; -- SELECT s.sid, DECODE(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body', 3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source', 14, 'Java Resource', 32, 'Java Data', TO_CHAR(ob.kglhdnsp)) objtyp, lk.kgllktype lktyp, DECODE(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', TO_CHAR(lk.kgllkmod)) lkmod, DECODE(lk.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', TO_CHAR(lk.kgllkreq)) lkreq, RAWTOHEX(lk.kgllkhdl) objaddr, DECODE(ob.kglnaown, NULL, '', ob.kglnaown || '.') || ob.kglnaobj || DECODE(ob.kglnadlk, NULL, '', '@' || ob.kglnadlk) objdef FROM v$session s, x$kglob ob, -- dba_kgllock lk ( SELECT kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype FROM x$kgllk UNION ALL SELECT kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype FROM x$kglpn ) lk WHERE lk.kgllkhdl = ob.kglhdadr AND lk.kgllkuse = s.saddr AND lk.kgllkhdl IN ( SELECT DISTINCT kgllkhdl FROM ( SELECT kgllkhdl, kgllkreq FROM x$kgllk UNION ALL SELECT kglpnhdl, kglpnreq FROM x$kglpn ) WHERE kgllkreq 0 ) ORDER BY lk.kgllkhdl, lk.kgllkreq ASC, lk.kgllkmod DESC / That's the best thing I can do to help you. On 2003.07.08 23:39, Chindarkar, Chetan (CONS FIN , Contractor) wrote: Here is the information for relevant SID. SID SEQ# EVENT -- -- P1TEXT P1 P1RAW -- P2TEXT P2 P2RAW -- P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE -- -- --- --- 156 43997 library cache lock handle address 3830864540 E4565A9C lock address 4121103412 F5A30C34 10*mode+namespace31 001F 0 101 WAITING And here is the info from v$lock : SELECT * FROM v$lock WHERE sid = 156 ; ADDR KADDR SID TYID1ID2 LMODEREQUEST CTIME BLOCK -- -- -- -- -- -- -- -- 01A83FD4 01A840A0156 TX 65635 213866 6 0 5011 0 F58941AC F58941C0156 TM1548379 0 3 0 5005 0 F5893B94 F5893BA8156 TM 2832 0 3 0 5011 0 F58939B4 F58939C8156 TM 870072 0 3 0 5005 0 F58938C4 F58938D8156 TM1548381 0 3 0 4495 0 F58913BC F58913D0156 TM1548382 0 3 0 4170 0
SQL*Loader problem - constraint violation
I'm new to sqlldr and here is my control file: LOAD DATA INFILE '/home/oracle/116.csv' BADFILE '/home/oracle/116.bad' DISCARDFILE '/home/oracle/116.rej' APPEND INTO TABLE LC_F FIELDS TERMINATED BY ',' TRAILING NULLCOLS (lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt, cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty ,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp) lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow? Here is a snippet from the log file: Record 1: Rejected - Error on table LC_F. ORA-1: unique constraint (TEST65W.LCI_LOC) violated LCI_LOC looks like this: CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) If anyone can help, it would be very appreciated. Thank you, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: sql loader question
Thursday, July 3, 2003, 5:40:37 AM, you wrote: bnini SQL*Loader reads a set of records from a file, generates INSERT bnini commands, This is the key right here. A conventional path load generates INSERT statements. A direct path load does not. A direct-path load passes preformatted blocks to the database. The term direct path comes from the fact that SQL*Loader (the client) formats a database block and sends it to the server to be written directly to the disk. No SQL processing is involved. bnini Answer : Direct-path load , ( It is said free blocks which implies bnini Direct-Path Load, because Conventional-Path Load also use bnini partially filled blocks. ) Was this from an Oracle exam? I hope not. It's ironic the person writing the test intended for you to notice the subtle mention of free blocks when they themselves didn't realize that direct path loads do not generate INSERT statements. bnini Shouldn't it be conventional path load, because insert commands are bnini being used . Yes. You are correct. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
sql loader question
Hello list I have a doubt regarding the following question : ** SQL*Loader reads a set of records from a file, generates INSERT commands, and passes them to the Oracle kernel. Oracle then finds places for those records in free blocks in the table and updates any associated indexes. Which SQL*Loader mode is used in this scenario? A. direct-path load B. conventional path load Answer : Direct-path load , ( It is said free blocks which implies Direct-Path Load, because Conventional-Path Load also use partially filled blocks. ) *** Shouldn't it be conventional path load, because insert commands are being used . Direct path load builds blocks of data in memory and saves these blocks directly into the extents allocated for the table being loaded -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: sql loader question
Since direct path loads do an append, I would say the answer shown is incorrect. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/03/2003 02:40 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:sql loader question Hello list I have a doubt regarding the following question : ** SQL*Loader reads a set of records from a file, generates INSERT commands, and passes them to the Oracle kernel. Oracle then finds places for those records in free blocks in the table and updates any associated indexes. Which SQL*Loader mode is used in this scenario? A. direct-path load B. conventional path load Answer : Direct-path load , ( It is said free blocks which implies Direct-Path Load, because Conventional-Path Load also use partially filled blocks. ) *** Shouldn't it be conventional path load, because insert commands are being used . Direct path load builds blocks of data in memory and saves these blocks directly into the extents allocated for the table being loaded -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL*Loader Help -- Multiple rows into single column
No. We are still with 8i. --- [EMAIL PROTECTED] wrote: Are you on 9i? If so, setup the sql_load.txt file as an external table, and you can then use SQL and/ora PL/SQL to load your table the way you would like. Don't think you can do what you're asking directly from sqlldr. Jared Bob Robert [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/26/2003 01:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL*Loader Help -- Multiple rows into single column Gurus, I have a special scenario to load data into tables with SQL*Loader. My SQL Loader data is not fixed format. It changes from time to time. But there is a good pattern about the data. Data which starts with letter R should go to table1 and data which starts with letter Z should go to table2. Table1 data is always fixed format where as table2 is kind of tricky. I would like to load data which starts with letter Z into table2 as a single row. For Example: (see my data at the bottom) Tom is having 3 lines of data Bob and Sam is having 4 lines of data Joe is having 5 lines of data Right now as per my SQL Loader Control file (sql_load.ctl), all the data which starts with Z goes into different rows (Tom -- 3 rows, Bob and Sam -- 4 rows, Joe -- 5 rows). I would like to load four rows into table1 (it is fine) and four rows into table2 (I am getting 16 rows). I hope I explained properly. FYI Please take look at the following scripts. -- Create Tables Script Start (sql_load.sql) -- Sequence create sequence table1_seq increment by 1 start with 1; create sequence table2_seq increment by 1 start with 1; -- Tables create table table1 (serial_no number(5), name varchar2(10), amount number(4)) / create table table2 (shipment_no number(5), details varchar2(1000)) / -- Create Tables Script End (sql_load.sql) -- SQL Loader Control file Start (sql_load.ctl) options (rows=1, errors=1) load data infile 'c:\sql_load.txt' badfile 'c:\sql_load.bad' discardfile 'c:\sql_load.disc' replace -- load table1 into table table1 when (1:1) = 'R' (serial_no position(1:1) table1_seq.nextval, name position(2:6) char, amount position(6:10) char) -- load table2 into table table2 when (1:1) = 'Z' (shipment_no position(1:1) table2_seq.nextval, details position(2:81) char) -- SQL Loader Control file End (sql_load.ctl) -- SQL Loader Data File Start (sql_load.txt) RTom 400 ZName: Tom ZShip_To: New York ZBill_To: Trenton RBob 300 ZName: Bob ZShip_To: Chicago ZBill_To: Detroit ZNotes: Best Customer Award RSam 500 ZName: Sam ZShip_To: Troy ZBill_To: Dallas ZNotes: Average Customer Award RJoe 200 ZName: Joe ZShip_To: Erie ZBill_To: San Fransisco ZNotes: Best Customer Award ZSpecial Notes: Include Customer -- SQL Loader Data File End (sql_load.txt) Thanks, Bob __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Constant Date in SQL*Loader Control File
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
RE: Constant Date in SQL*Loader Control File
Hi David,
Here's one of my control files, it works for me... LOAD_DATE is indeed a date field.
OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE ASCNAME_RAW
(
ID RECNUM,
AN_SITE POSITION(1:2) CHAR
NULLIF AN_SITE=BLANKS,
AN_ASSOC_NO POSITION(3:6) CHAR
NULLIF AN_ASSOC_NO=BLANKS,
AN_ASSOC_NAME POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
LOAD_DATE SYSDATE
)
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459
-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
RE: Constant Date in SQL*Loader Control File
David,
Try removing the word CONSTANT from the string.
LOADDATE TO_DATE('20030626', 'MMDD'),
Ron
[EMAIL PROTECTED] 06/26/03 02:09PM
Hi David,
Here's one of my control files, it works for me... LOAD_DATE is indeed
a date field.
OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE ASCNAME_RAW
(
ID RECNUM,
AN_SITE POSITION(1:2) CHAR
NULLIF AN_SITE=BLANKS,
AN_ASSOC_NO POSITION(3:6) CHAR
NULLIF AN_ASSOC_NO=BLANKS,
AN_ASSOC_NAME POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
LOAD_DATE SYSDATE
)
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459
-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was
expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
RE: Constant Date in SQL*Loader Control File
Thanks Lisa. But regrettably my LOADDATE isn't always SYSDATE. Do you know
the syntax for an arbitrary date?
Dave
-Original Message-
Sent: Thursday, June 26, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Hi David,
Here's one of my control files, it works for me... LOAD_DATE is indeed a
date field.
OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE ASCNAME_RAW
(
ID RECNUM,
AN_SITE POSITION(1:2) CHAR
NULLIF AN_SITE=BLANKS,
AN_ASSOC_NO POSITION(3:6) CHAR
NULLIF AN_ASSOC_NO=BLANKS,
AN_ASSOC_NAME POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
LOAD_DATE SYSDATE
)
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459
-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
RE: Constant Date in SQL*Loader Control File
Dave,
I believe if you specify in the .cal file:
loaddate to_date('20030626', 'MMDD'),
That will default the date to what you want. Now if the date is sometimes specified
that's a different story.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Thursday, June 26, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L
Thanks Lisa. But regrettably my LOADDATE isn't always SYSDATE. Do you know
the syntax for an arbitrary date?
Dave
-Original Message-
Sent: Thursday, June 26, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Hi David,
Here's one of my control files, it works for me... LOAD_DATE is indeed a
date field.
OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE ASCNAME_RAW
(
ID RECNUM,
AN_SITE POSITION(1:2) CHAR
NULLIF AN_SITE=BLANKS,
AN_ASSOC_NO POSITION(3:6) CHAR
NULLIF AN_ASSOC_NO=BLANKS,
AN_ASSOC_NAME POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
LOAD_DATE SYSDATE
)
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459
-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Goulet, Dick
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
RE: Constant Date in SQL*Loader Control File
Hi David,
Ron's suggestion worked, I just tried it:
LOAD_DATE TO_DATE('010103','MMDDYY')
Give it a try.
-Original Message-
Sent: Thursday, June 26, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L
Thanks Lisa. But regrettably my LOADDATE isn't always SYSDATE. Do you know
the syntax for an arbitrary date?
Dave
-Original Message-
Sent: Thursday, June 26, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Hi David,
Here's one of my control files, it works for me... LOAD_DATE is indeed a
date field.
OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE ASCNAME_RAW
(
ID RECNUM,
AN_SITE POSITION(1:2) CHAR
NULLIF AN_SITE=BLANKS,
AN_ASSOC_NO POSITION(3:6) CHAR
NULLIF AN_ASSOC_NO=BLANKS,
AN_ASSOC_NAME POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
LOAD_DATE SYSDATE
)
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459
-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
The sender believes that this E-Mail and any attachments were free of any virus,
worm, Trojan horse, and/or malicious code when sent. This message and its attachments
could have been infected during transmission. By reading the message and opening any
attachments, the recipient accepts full responsibility for taking proactive and
remedial action about viruses and other defects. The sender's business entity is not
liable for any loss or damage arising in any way from this message or its attachments.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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
Re: Constant Date in SQL*Loader Control File
David,
Have you tried removing the around the TO_DATE call? The indicate a
character string and Oracle is unable to translate that string into something
acceptable for a DATE datatype.
David Lewandowski wrote:
Thanks Lisa. But regrettably my LOADDATE isn't always SYSDATE. Do you know
the syntax for an arbitrary date?
Dave
-Original Message-
Sent: Thursday, June 26, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Hi David,
Here's one of my control files, it works for me... LOAD_DATE is indeed a
date field.
OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE ASCNAME_RAW
(
ID RECNUM,
AN_SITE POSITION(1:2) CHAR
NULLIF AN_SITE=BLANKS,
AN_ASSOC_NO POSITION(3:6) CHAR
NULLIF AN_ASSOC_NO=BLANKS,
AN_ASSOC_NAME POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
LOAD_DATE SYSDATE
)
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459
-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard
SQL*Loader Help -- Multiple rows into single column
Gurus, I have a special scenario to load data into tables with SQL*Loader. My SQL Loader data is not fixed format. It changes from time to time. But there is a good pattern about the data. Data which starts with letter R should go to table1 and data which starts with letter Z should go to table2. Table1 data is always fixed format where as table2 is kind of tricky. I would like to load data which starts with letter Z into table2 as a single row. For Example: (see my data at the bottom) Tom is having 3 lines of data Bob and Sam is having 4 lines of data Joe is having 5 lines of data Right now as per my SQL Loader Control file (sql_load.ctl), all the data which starts with Z goes into different rows (Tom -- 3 rows, Bob and Sam -- 4 rows, Joe -- 5 rows). I would like to load four rows into table1 (it is fine) and four rows into table2 (I am getting 16 rows). I hope I explained properly. FYI Please take look at the following scripts. -- Create Tables Script Start (sql_load.sql) -- Sequence create sequence table1_seq increment by 1 start with 1; create sequence table2_seq increment by 1 start with 1; -- Tables create table table1 (serial_no number(5), name varchar2(10), amount number(4)) / create table table2 (shipment_no number(5), details varchar2(1000)) / -- Create Tables Script End (sql_load.sql) -- SQL Loader Control file Start (sql_load.ctl) options (rows=1, errors=1) load data infile 'c:\sql_load.txt' badfile 'c:\sql_load.bad' discardfile 'c:\sql_load.disc' replace -- load table1 into table table1 when (1:1) = 'R' (serial_no position(1:1) table1_seq.nextval, name position(2:6) char, amount position(6:10) char) -- load table2 into table table2 when (1:1) = 'Z' (shipment_no position(1:1) table2_seq.nextval, details position(2:81) char) -- SQL Loader Control file End (sql_load.ctl) -- SQL Loader Data File Start (sql_load.txt) RTom 400 ZName: Tom ZShip_To: New York ZBill_To: Trenton RBob 300 ZName: Bob ZShip_To: Chicago ZBill_To: Detroit ZNotes: Best Customer Award RSam 500 ZName: Sam ZShip_To: Troy ZBill_To: Dallas ZNotes: Average Customer Award RJoe 200 ZName: Joe ZShip_To: Erie ZBill_To: San Fransisco ZNotes: Best Customer Award ZSpecial Notes: Include Customer -- SQL Loader Data File End (sql_load.txt) Thanks, Bob __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Constant Date in SQL*Loader Control File
That's it. Thanks Ron and everyone else for your help. Dave
-Original Message-
Sent: Thursday, June 26, 2003 1:51 PM
To: Multiple recipients of list ORACLE-L
David,
Try removing the word CONSTANT from the string.
LOADDATE TO_DATE('20030626', 'MMDD'),
Ron
[EMAIL PROTECTED] 06/26/03 02:09PM
Hi David,
Here's one of my control files, it works for me... LOAD_DATE is indeed
a date field.
OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
UNRECOVERABLE
LOAD DATA
INFILE 'D:\FTPRoot\vegas\ascname.txt'
BADFILE 'D:\data\vegas\log\ascname.bad'
DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
TRUNCATE
INTO TABLE ASCNAME_RAW
(
ID RECNUM,
AN_SITE POSITION(1:2) CHAR
NULLIF AN_SITE=BLANKS,
AN_ASSOC_NO POSITION(3:6) CHAR
NULLIF AN_ASSOC_NO=BLANKS,
AN_ASSOC_NAME POSITION(7:36) CHAR
NULLIF AN_ASSOC_NAME=BLANKS,
AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
LOAD_DATE SYSDATE
)
Lisa Koivu
Oracle Database Monkey
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459
-Original Message-
Sent: Thursday, June 26, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
I can't figure out how to specify a constant date for a column
in a SQL*Loader control file and can't find any examples.
I tried:
LOADDATE CONSTANT TO_DATE('20030626', 'MMDD'),
but I get this error message in the log file:
ORA-01858: a non-numeric character was found where a numeric was
expected
Can anyone tell me the correct syntax?
David Lewandowski
Focused Health Solutions
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: David Lewandowski
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
RE: SQL*Loader Help -- Multiple rows into single column
Bob If you were on Unix, I would suggest you have a script that split your input file into multiple files using one of the Unix utilities, then executed SQL*Loader against each of those. Since you are on Windows, perhaps someone has a suggestion that will apply there. I know there are Windows versions of the Unix utilities floating around. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, June 26, 2003 3:40 PM To: Multiple recipients of list ORACLE-L Gurus, I have a special scenario to load data into tables with SQL*Loader. My SQL Loader data is not fixed format. It changes from time to time. But there is a good pattern about the data. Data which starts with letter R should go to table1 and data which starts with letter Z should go to table2. Table1 data is always fixed format where as table2 is kind of tricky. I would like to load data which starts with letter Z into table2 as a single row. For Example: (see my data at the bottom) Tom is having 3 lines of data Bob and Sam is having 4 lines of data Joe is having 5 lines of data Right now as per my SQL Loader Control file (sql_load.ctl), all the data which starts with Z goes into different rows (Tom -- 3 rows, Bob and Sam -- 4 rows, Joe -- 5 rows). I would like to load four rows into table1 (it is fine) and four rows into table2 (I am getting 16 rows). I hope I explained properly. FYI Please take look at the following scripts. -- Create Tables Script Start (sql_load.sql) -- Sequence create sequence table1_seq increment by 1 start with 1; create sequence table2_seq increment by 1 start with 1; -- Tables create table table1 (serial_no number(5), name varchar2(10), amount number(4)) / create table table2 (shipment_no number(5), details varchar2(1000)) / -- Create Tables Script End (sql_load.sql) -- SQL Loader Control file Start (sql_load.ctl) options (rows=1, errors=1) load data infile 'c:\sql_load.txt' badfile 'c:\sql_load.bad' discardfile 'c:\sql_load.disc' replace -- load table1 into table table1 when (1:1) = 'R' (serial_no position(1:1) table1_seq.nextval, name position(2:6) char, amount position(6:10) char) -- load table2 into table table2 when (1:1) = 'Z' (shipment_no position(1:1) table2_seq.nextval, details position(2:81) char) -- SQL Loader Control file End (sql_load.ctl) -- SQL Loader Data File Start (sql_load.txt) RTom 400 ZName: Tom ZShip_To: New York ZBill_To: Trenton RBob 300 ZName: Bob ZShip_To: Chicago ZBill_To: Detroit ZNotes: Best Customer Award RSam 500 ZName: Sam ZShip_To: Troy ZBill_To: Dallas ZNotes: Average Customer Award RJoe 200 ZName: Joe ZShip_To: Erie ZBill_To: San Fransisco ZNotes: Best Customer Award ZSpecial Notes: Include Customer -- SQL Loader Data File End (sql_load.txt) Thanks, Bob __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL*Loader Help -- Multiple rows into single column
Are you on 9i? If so, setup the sql_load.txt file as an external table, and you can then use SQL and/ora PL/SQL to load your table the way you would like. Don't think you can do what you're asking directly from sqlldr. Jared Bob Robert [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/26/2003 01:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL*Loader Help -- Multiple rows into single column Gurus, I have a special scenario to load data into tables with SQL*Loader. My SQL Loader data is not fixed format. It changes from time to time. But there is a good pattern about the data. Data which starts with letter R should go to table1 and data which starts with letter Z should go to table2. Table1 data is always fixed format where as table2 is kind of tricky. I would like to load data which starts with letter Z into table2 as a single row. For Example: (see my data at the bottom) Tom is having 3 lines of data Bob and Sam is having 4 lines of data Joe is having 5 lines of data Right now as per my SQL Loader Control file (sql_load.ctl), all the data which starts with Z goes into different rows (Tom -- 3 rows, Bob and Sam -- 4 rows, Joe -- 5 rows). I would like to load four rows into table1 (it is fine) and four rows into table2 (I am getting 16 rows). I hope I explained properly. FYI Please take look at the following scripts. -- Create Tables Script Start (sql_load.sql) -- Sequence create sequence table1_seq increment by 1 start with 1; create sequence table2_seq increment by 1 start with 1; -- Tables create table table1 (serial_no number(5), name varchar2(10), amount number(4)) / create table table2 (shipment_no number(5), details varchar2(1000)) / -- Create Tables Script End (sql_load.sql) -- SQL Loader Control file Start (sql_load.ctl) options (rows=1, errors=1) load data infile 'c:\sql_load.txt' badfile 'c:\sql_load.bad' discardfile 'c:\sql_load.disc' replace -- load table1 into table table1 when (1:1) = 'R' (serial_no position(1:1) table1_seq.nextval, name position(2:6) char, amount position(6:10) char) -- load table2 into table table2 when (1:1) = 'Z' (shipment_no position(1:1) table2_seq.nextval, details position(2:81) char) -- SQL Loader Control file End (sql_load.ctl) -- SQL Loader Data File Start (sql_load.txt) RTom 400 ZName: Tom ZShip_To: New York ZBill_To: Trenton RBob 300 ZName: Bob ZShip_To: Chicago ZBill_To: Detroit ZNotes: Best Customer Award RSam 500 ZName: Sam ZShip_To: Troy ZBill_To: Dallas ZNotes: Average Customer Award RJoe 200 ZName: Joe ZShip_To: Erie ZBill_To: San Fransisco ZNotes: Best Customer Award ZSpecial Notes: Include Customer -- SQL Loader Data File End (sql_load.txt) Thanks, Bob __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL Loader
Title: RE: SQL Loader If SQL loader loads a table it takes the first record in the file and stuffs it in the top of the table... like a stack... last in, first out so if you were to read the table like you read the sequential file, it is in there with the bottom record in the file as the top row in the table...so SQL loader is, in effect, going from the first record in the file, it just pushes it on the stack... and when you are reading you are popping it off (although leaving it physically in place where a stack would pop it off and then discard it). I haven't really played a lot with external tables, but I would imagine that Kirti's explanation is quite logical. It takes the first block of records in the external table, and inserts it as the first (or next if you are appending it) block in the regular table... so it appears to be in the correct order. I guess that, in a sense then, that external tables are more of a queue type structure... filling up top to bottom... ajw April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas You will recognize your own path when you come upon it, because you will suddenly have all the energy and imagination you will ever need. ~ Jerry Gillies ~ -Original Message- From: Pradeep Kumar G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 03, 2003 6:00 AM To: Multiple recipients of list ORACLE-L Subject: Re: SQL Loader When I queried the table to which SQL Loader loaded data from the .dat file, the first record in the table was the 5th record and 4,49998... Kirtikumar Deshpande kirtikumar_deshpande@ To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.com cc: Sent by: Subject: Re: SQL Loader [EMAIL PROTECTED] 06/02/03 11:02 PM Please respond to ORACLE-L I think the query is displaying data from the table in the order in which rows were inserted by SQL*Loader. Whereas, the query to read the same data from the external table is reading the file from the first physical record. So it appears reversed.. - Kirti Stephen Andert [EMAIL PROTECTED] wrote: Pradeep, I don't know what you mean when you say SQL*Loader is starting from the 50,000th record and going up. If there are 50k records, are you saying it is not loading them? Or is it reading them from 50,000 then 49,999 (i.e. in revers order)? How are you determining what order they are being read? Regarding external tables, the way they work is by reading the file from the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in the file. I stand ready for my ramblings to be corrected by anyone who can explain better :) Stephen [EMAIL PROTECTED] 06/01/03 10:35PM Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that S! QL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep Do you Yahoo!? Free online calendar with sync to Outlook(TM). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pradeep Kumar G INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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). The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
Re: SQL Loader
That's what I thought. LIFO. Why does the order matter? Can you sort the result to suit your needs? - Kirti --- Pradeep Kumar G [EMAIL PROTECTED] wrote: When I queried the table to which SQL Loader loaded data from the .dat file, the first record in the table was the 5th record and 4,49998... kirtikumar_deshpande@To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.comcc: Sent by: Subject: Re: SQL Loader [EMAIL PROTECTED] I think the query is displaying data from the table in the order in which rows were inserted by SQL*Loader. Whereas, the query to read the same data from the external table is reading the file from the first physical record. So it appears reversed.. - Kirti Stephen Andert [EMAIL PROTECTED] wrote: Pradeep, I don't know what you mean when you say SQL*Loader is starting from the 50,000th record and going up. If there are 50k records, are you saying it is not loading them? Or is it reading them from 50,000 then 49,999 (i.e. in revers order)? How are you determining what order they are being read? Regarding external tables, the way they work is by reading the file from the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in the file. I stand ready for my ramblings to be corrected by anyone who can explain better :) Stephen [EMAIL PROTECTED] 06/01/03 10:35PM Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that S! QL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader
Pradeep, I don't know what you mean when you say SQL*Loader is starting from the 50,000th record and going up. If there are 50k records, are you saying it is not loading them? Or is it reading them from 50,000 then 49,999 (i.e. in revers order)? How are you determining what order they are being read? Regarding external tables, the way they work is by reading the file from the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in the file. I stand ready for my ramblings to be corrected by anyone who can explain better :) Stephen [EMAIL PROTECTED] 06/01/03 10:35PM Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that SQL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pradeep Kumar G INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader
I think the query isdisplaying data from the tablein the order in which rows were inserted by SQL*Loader. Whereas, the query to read the same data from the external table is reading the file from the first physical record. So it appears reversed.. - Kirti Stephen Andert [EMAIL PROTECTED] wrote: Pradeep, I don't know what you mean when you say SQL*Loader is "starting fromthe 50,000th record and going up". If there are 50k records, are yousaying it is not loading them? Or is it reading them from 50,000 then49,999 (i.e. in revers order)? How are you determining what order theyare being read? Regarding external tables, the way they work is by reading the filefrom the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in thefile. I stand ready for my ramblings to be corrected by anyone who canexplain better :)Stephen [EMAIL PROTECTED] 06/01/03 10:35PM Dear All,I don't know whether SQL Loader behaves like this always or for meonly. I am loading a .dat file which has 5 records.I noticed that S! QL loader is starting from the 50,000th record andgoing upwards, while external tables is starting from the 1st recordand going downwards.Could somebody help me confirming this?Regards,Pradeep Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: SQL Loader
When I queried the table to which SQL Loader loaded data from the .dat file, the first record in the table was the 5th record and 4,49998... Kirtikumar Deshpande kirtikumar_deshpande@To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.comcc: Sent by: Subject: Re: SQL Loader [EMAIL PROTECTED] 06/02/03 11:02 PM Please respond to ORACLE-L I think the query is displaying data from the table in the order in which rows were inserted by SQL*Loader. Whereas, the query to read the same data from the external table is reading the file from the first physical record. So it appears reversed.. - Kirti Stephen Andert [EMAIL PROTECTED] wrote: Pradeep, I don't know what you mean when you say SQL*Loader is starting from the 50,000th record and going up. If there are 50k records, are you saying it is not loading them? Or is it reading them from 50,000 then 49,999 (i.e. in revers order)? How are you determining what order they are being read? Regarding external tables, the way they work is by reading the file from the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in the file. I stand ready for my ramblings to be corrected by anyone who can explain better :) Stephen [EMAIL PROTECTED] 06/01/03 10:35PM Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that S! QL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep Do you Yahoo!? Free online calendar with sync to Outlook(TM). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pradeep Kumar G INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: performance of sql loader
Regarding #2, you also need to be aware of how parameters interact with each other. For example in non-direct loads, ROWS and BINDSIZE work hand-in-hand. Increasing one but not the other (or not enough) will bottleneck and it will use a smaller array to load. Stephen [EMAIL PROTECTED] 05/30/03 10:44PM On Friday 30 May 2003 12:20, [EMAIL PROTECTED] wrote: 2. As they increased the Array size or the commit size, the performance degradated rapidly. This sounds odd. Anyone else notice this? Or did they just do something wrong. Dont know what they did. They tried it before I started, I just have hearsay to go on. Sorry about the lack of details. Not enough information. Increased from what? To what? If you increase the array size enough to start swapping, it may have a negative impact on performance. ;) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL Loader
Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that SQL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pradeep Kumar G INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re[2]: SQL Loader Concatenate date and time
Thursday, May 29, 2003, 6:00:49 PM, Bob wrote: BM Thanks for answering my question. I diddnt realise you could BM querry colums ahead of the current line. As I recall, it took me a long time to realize that too. It may help to realize that everything you type between double-quotes (those SQL expressions) are made part of the INSERT statement SQL*Loader uses to insert each row. By the time that INSERT gets executed, SQL*Loader has to have parsed the input record and isolated all the fields. Thus, in a SQL expression, you have access to all the fields all the time. Related to the above, before Oracle9i, using SQL expressions with SQL*Loader precludes doing a direct-path load. 9i relaxed that restriction somewhat. It's been awhile though, since I've looked at exactly what 9i allows. You can see your SQL expressions in the INSERT statement. Just enable SQL tracing while you run one of your loads. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
performance of sql loader
I was talking to some colleagues and they did the following tests. I was wondering if anyone else had similiar results or maybe they just didnt do it properly. They are using standard SQLLOADER. No direct path inserts and doing some SQL data manipulation of the files. They found the following: 1. SQLLOADER with the SQL manipulation is much slower than Direct Path SQLLOADER to a staging table, then insert,update, and delete to the master table. 2. As they increased the Array size or the commit size, the performance degradated rapidly. This sounds odd. Anyone else notice this? Or did they just do something wrong. Dont know what they did. They tried it before I started, I just have hearsay to go on. Sorry about the lack of details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: performance of sql loader
On Friday 30 May 2003 12:20, [EMAIL PROTECTED] wrote: 1. SQLLOADER with the SQL manipulation is much slower than Direct Path SQLLOADER to a staging table, then insert,update, and delete to the master table. Sounds about right. It's been awhile since making heavy use of SQL Loader, but DIRECT is very fast. Not surprising that manipulation could be done after loading a temp table and still be faster than normal SQL Loader. 2. As they increased the Array size or the commit size, the performance degradated rapidly. This sounds odd. Anyone else notice this? Or did they just do something wrong. Dont know what they did. They tried it before I started, I just have hearsay to go on. Sorry about the lack of details. Not enough information. Increased from what? To what? If you increase the array size enough to start swapping, it may have a negative impact on performance. ;) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL Loader Concatenate date and time
All Im need to concatenate my log_date and log_time field (the 2 physical records into one logical record). I can find how to do it with a fixed length file but my case is a delimited file. Looking at the docs, it seems the continueif is used for delimited dtaa, I cant seem to get the syntax to work any ideas would be welcome ;-) thanks! Bob LOAD DATA INFILE 'F:\528log.txt' BADFILE 'F:\bad.txt' truncate INTO TABLE log FIELDS TERMINATED BY WHITESPACE OPTIONALLY enclosed by '' TRAILING( log_id INTEGER, log_date DATE 'DD-MON-', log_time char, vpn_type char, interface char, name char, type char, action char, service char, source char, destination char, protocol char, port char, service2 char, log_user char, message char) example source row data 283700 28May2003 16:28:12 fff eth-sfp1c0 fff Log Accept nbdatagram 10.54.4.1 10.54.255.255 udp 23 nbdatagram Example oracle row data 283700 05/28/2003 00:00:00 16:28:12fff eth-s4fp1c0 fff Log Accept nbdatagram 10.54.4.1 10.54.255.255 udp 23 nbdatagram -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL Loader Concatenate date and time
Hello Bob, My first thought is to try something like: ... log_date CHAR TO_DATE(:log_date || ' ' || :log_time,'ddMon hh:mi:ss', log_time FILLER char, ... I may not have the syntax just right, and I can't take time to test it until later this evening (shouldn't be reading ORACLE-L now anywaygrin), but I'm fairly certain this approach can be made to work. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. Thursday, May 29, 2003, 11:54:44 AM, you wrote: BM All BM Im need to concatenate my log_date and log_time field (the 2 BM physical records into one logical record). BM I can find how to do it with a fixed length file but my case is a BM delimited file. Looking at the docs, it seems the continueif is used for BM delimited dtaa, I cant seem to get the syntax to work BM any ideas would be welcome ;-) BM thanks! BM Bob BM LOAD DATA BM INFILE 'F:\528log.txt' BM BADFILE 'F:\bad.txt' BM truncate BM INTO TABLE log BM FIELDS TERMINATED BY WHITESPACE OPTIONALLY enclosed by '' BM TRAILING( BM log_id INTEGER, BM log_date DATE 'DD-MON-', BM log_time char, BM vpn_type char, BM interface char, BM name char, BM type char, BM action char, BM service char, BM source char, BM destination char, BM protocol char, BM port char, BM service2 char, BM log_user char, BM message char) BM example source row data BM 283700 28May2003 16:28:12 fff eth-sfp1c0 fff Log Accept BM nbdatagram 10.54.4.1 10.54.255.255 udp 23 nbdatagram BM Example oracle row data BM 283700 05/28/2003 00:00:00 16:28:12fff eth-s4fp1c0 BM fff Log Accept nbdatagram 10.54.4.1 10.54.255.255 BM udp 23 nbdatagram BM -- BM Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: SQL Loader Concatenate date and time
Hi Jonathan Thanks for answering my question. I diddnt realise you could querry colums ahead of the current line. Ive applied functions (in a ctl file) to the current column, but diddnt realise I could || the next column My working ctl file Thanks again bob LOAD DATA INFILE 'F:\11NETSHARE\528fwlog.txt' BADFILE 'F:\11NETSHARE\mybad.txt' append INTO TABLE fw_log FIELDS TERMINATED BY WHITESPACE OPTIONALLY enclosed by '' TRAILING( id SEQUENCE(MAX,1), log_id char, log_date char TO_DATE(:log_date || ' ' || :log_time,'ddMon hh24:mi:ss'), log_time CHAR, vpn_type char, interface char, name char, type char, action char, service char, source char, destination char, protocol char, port char, service2 char, log_user char, message char, create_date sysdate) Hello Bob, My first thought is to try something like: ... log_date CHAR TO_DATE(:log_date || ' ' || :log_time,'ddMon hh:mi:ss', log_time FILLER char, ... I may not have the syntax just right, and I can't take time to test it until later this evening (shouldn't be reading ORACLE-L now anywaygrin), but I'm fairly certain this approach can be made to work. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. Thursday, May 29, 2003, 11:54:44 AM, you wrote: BM All BM Im need to concatenate my log_date and log_time field (the BM 2 physical records into one logical record). I can find how to do BM it with a fixed length file but my case is a delimited file. Looking BM at the docs, it seems the continueif is used for delimited dtaa, I BM cant seem to get the syntax to work BM any ideas would be welcome ;-) BM thanks! BM Bob BM LOAD DATA BM INFILE 'F:\528log.txt' BM BADFILE 'F:\bad.txt' BM truncate BM INTO TABLE log BM FIELDS TERMINATED BY WHITESPACE OPTIONALLY enclosed by '' BM TRAILING( BM log_id INTEGER, BM log_date DATE 'DD-MON-', BM log_time char, BM vpn_type char, BM interface char, BM name char, BM type char, BM action char, BM service char, BM source char, BM destination char, BM protocol char, BM port char, BM service2 char, BM log_user char, BM message char) BM example source row data BM 283700 28May2003 16:28:12 fff eth-sfp1c0 fff Log BM Accept nbdatagram 10.54.4.1 10.54.255.255 udp 23 BM nbdatagram BM Example oracle row data BM 283700 05/28/2003 00:00:00 16:28:12fff eth-s4fp1c0 BM fff Log Accept nbdatagram 10.54.4.1 10.54.255.255 BM udp 23 nbdatagram BM -- BM Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
SQL*LOADER question
Hi All, I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i at a remote site (WAN connection) and another on my desk running the Oracle client software(no database). Can I, and if so how, use sqlldr on my desktop PC to load data into the remote database. The data file to be loaded and the control file are on my desktop. I do not want to use netmeeting or PC anywhere type programs which I know I can use. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: SQL*LOADER question
Should not be a problem: sqlldr control=your_ctl_file data=your_data_file userid=login/pwd@remote_machine where remote_machine is an entry in your local/client tnsnames file. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 8:23 AM Hi All, I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i at a remote site (WAN connection) and another on my desk running the Oracle client software(no database). Can I, and if so how, use sqlldr on my desktop PC to load data into the remote database. The data file to be loaded and the control file are on my desktop. I do not want to use netmeeting or PC anywhere type programs which I know I can use. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
