There are two options to replace all data in the table: REPLACE and TRUNCATE which are equivalent to truncate and delete sql statements. If you have staging tables without RI or triggers then use truncate. Using delete just takes a lot longer and use a lot more resources.
We use TRUNCATE almost exclusively. Yechiel Adar Mehish ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 3:44 PM > Hi > > We do something similiar, but instead of deleting the tables beforehand, I > just use the SQL*LOADER REPLACE option. No such problems as described in the > original eMail occured so far. The platform is Oracle 9.2.0.3 on Win3k. > > Regards, > Stefan > > -----Urspr�ngliche Nachricht----- > Von: Jonathan Gennick [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 12. November 2003 14:34 > An: Multiple recipients of list ORACLE-L > Betreff: Fwd: Looking for help. > > > I don't usually forward my reader email to the list, but the > question below strikes me as rather interesting. In this > case, SQL*Loader appears to be causing all SQL statements > that refer to the table being loaded to be invalidated. Is > this normal behavior? Does anyone know why it might be the > case? > > -- > 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. > > Wednesday, November 12, 2003, 1:07:41 AM, > [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: > Hi Jonathan, > > I was unable to find the answers from your book "SQL*Loader: The Definitive > Guide" and the web. I am running out of sources. I hope you can help me with > the following questions. > > We are using Oracle 9i sqlldr, direct path to load data from external files > into > staging tables. After data is loaded, we invoked stored procedures to > transform data and move them to the target tables. The steps are: > 1. delete all entries from 20 staging tables > 2. invoke "sqlldr userid=dbimpl/dbimpl control=<controlFile> direct=true" to > load data to all 20 staging tables > 3. invoke stored procedures to transform data from the staging tables to the > final tables. Currently these stored procedures are standalone. > 4. invoke stored procedures to remove out-of-date entries from the final > tables. > > I monitor invalidations column in v$sqlarea. Every time > after sqlldr is invoked for data loading (step 2), all the > sql statements that reference the staging tables are > invalidated, including "delete from <stageing_table>" sql > statement. I setup a test and used a java program to loop > steps 1-4 every ~2 minutes. There were no other activities > in the database except data loading and transformation. > After a couple days, I got the following error: ORA-04031: > unable to allocate 4212 bytes of shared memory ("shared > pool","unknown object","sga heap(1,0)","stat array mem") > > The questions are: > 1. Do we need to delete entries in the staging table prior to loading. Will > sqlldr remove the entires in the staging table first prior to loading? > 2. There are no changes in the stored procedures, how / why sqlldr would > invalidate the sql statement in the stored procedures? > 3. The error ORA-04031 in this case, is it due to shared memory > fragmentation? I suspect that the culprint is invalidations. How do > invalidations cause shared memory fragmentation? > > I would appreciate if you can send me some pointers or suggestions. > > Thanks, > KamYee > > -- > 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar 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).
