Jonathan, I don't see where truncate is being invoked, though I am not a big user of sql*loader. Truncate should invalidate statements.
Daniel Jonathan Gennick wrote: > I wonder whether the invalidation comes about from the use > of TRUNCATE, which is considered a DDL statement. I'd guess > that any DDL to a table would invalidate existing SQL > statements. > > 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, 8:34:24 AM, Jonathan Gennick ([EMAIL PROTECTED]) wrote: > JG> I don't usually forward my reader email to the list, but the > JG> question below strikes me as rather interesting. In this > JG> case, SQL*Loader appears to be causing all SQL statements > JG> that refer to the table being loaded to be invalidated. Is > JG> this normal behavior? Does anyone know why it might be the > JG> case? > > JG> -- > JG> Best regards, > > JG> Jonathan Gennick --- Brighten the corner where you are > JG> http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > JG> Join the Oracle-article list and receive one > JG> article on Oracle technologies per month by > JG> email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, > JG> or send email to [EMAIL PROTECTED] and > JG> include the word "subscribe" in either the subject or body. > > JG> Wednesday, November 12, 2003, 1:07:41 AM, > JG> [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: > JG> Hi Jonathan, > > JG> 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. > > JG> We are using Oracle 9i sqlldr, direct path to load data from external files into > JG> staging tables. After data is loaded, we invoked stored procedures to > JG> transform data and move them to the target tables. The steps are: > JG> 1. delete all entries from 20 staging tables > JG> 2. invoke "sqlldr userid=dbimpl/dbimpl control=<controlFile> direct=true" to > JG> load data to all 20 staging tables > JG> 3. invoke stored procedures to transform data from the staging tables to the > JG> final tables. Currently these stored procedures are standalone. > JG> 4. invoke stored procedures to remove out-of-date entries from the final > JG> tables. > > JG> I monitor invalidations column in v$sqlarea. Every time > JG> after sqlldr is invoked for data loading (step 2), all the > JG> sql statements that reference the staging tables are > JG> invalidated, including "delete from <stageing_table>" sql > JG> statement. I setup a test and used a java program to loop > JG> steps 1-4 every ~2 minutes. There were no other activities > JG> in the database except data loading and transformation. > JG> After a couple days, I got the following error: ORA-04031: > JG> unable to allocate 4212 bytes of shared memory ("shared > JG> pool","unknown object","sga heap(1,0)","stat array mem") > > JG> The questions are: > JG> 1. Do we need to delete entries in the staging table prior to loading. Will > JG> sqlldr remove the entires in the staging table first prior to loading? > JG> 2. There are no changes in the stored procedures, how / why sqlldr would > JG> invalidate the sql statement in the stored procedures? > JG> 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? > > JG> I would appreciate if you can send me some pointers or suggestions. > > JG> Thanks, > JG> KamYee > > JG> -- > JG> 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: Daniel Fink 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).
