Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fwd: Looking for help.

Re: Fwd: Looking for help.

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Wed, 12 Nov 2003 09:44:46 -0800
Message-ID: <F001.005D670A.20031112094446@fatcity.com>


Daniel,
 How does using the TRUNCATE command is a sqlldr invalidate anything? The sqlldr truncate command reuses the storage that the table originally used and does not change the HW mark. If there are indexes on the tables then they are placed in the "DIRECT PATH" state during the load and updated with the now block info.
 Please explain whet you mean by "invalidate". Ron

>>> Daniel.Fink_at_Sun.COM 11/12/2003 12:04:35 PM >>>
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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).
Received on Wed Nov 12 2003 - 11:44:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US