Re: Fwd: Looking for help.

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:jonathan@gennick.com
>
> 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 Oracle-article-request_at_gennick.com and
> include the word "subscribe" in either the subject or body.
>
> Wednesday, November 12, 2003, 8:34:24 AM, Jonathan Gennick (jonathan_at_gennick.com) 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:jonathan@gennick.com
>
> 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 Oracle-article-request_at_gennick.com and
> JG> include the word "subscribe" in either the subject or body.
>
> JG> Wednesday, November 12, 2003, 1:07:41 AM,
> JG> kamyeelee_at_comcast.net (kamyeelee_at_comcast.net) 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: jonathan_at_gennick.com
>
> 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: ListGuru_at_fatcity.com (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: Daniel.Fink_at_Sun.COM

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: ListGuru_at_fatcity.com (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).