.. 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.
Stefan -----Urspr�ngliche Nachricht----- Von: Rachel Carmichael [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 12. November 2003 18:45 An: Multiple recipients of list ORACLE-L Betreff: Re: Looking for help. yeah but... if you attempt (as I do) to isolate the schema owner from the users which have select/insert/update/delete privileges, TRUNCATE won't work unless you have granted DROP ANY TABLE (I *really* hate that that is required) to the account which does the actual sql load. So I use REPLACE, because then I only have to grant the delete priv on that table. --- Yechiel Adar <[EMAIL PROTECTED]> wrote: > 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). __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: 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).
