Or... Create a stored procedure that truncates the table... Grant execute on the procedure to the user... The user executes the procedure and then calls sqlldr...
Tim -----Original Message----- Sent: Wednesday, November 12, 2003 12:45 PM To: Multiple recipients of list ORACLE-L 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: Johnston, Tim 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).
