AW: Looking for help.

Ok. That's of course true, but I guess it depends on the scenario. With the truncate option, there is no chance to roll back the activity. If you use direct path load, I guess it makes more sense to opt for truncate.

Stefan

-----Ursprüngliche Nachricht-----
Von: Yechiel Adar [mailto:adar76_at_inter.net.il] Gesendet: Mittwoch, 12. November 2003 18:24 An: Multiple recipients of list ORACLE-L Betreff: Re: Looking for help.

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" <ORACLE-L_at_fatcity.com> 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:jonathan_at_gennick.com]
> 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: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, 1:07:41 AM,
> kamyeelee_at_comcast.net (kamyeelee_at_comcast.net) 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: 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: Stefan Jahnke
> INET: Stefan.Jahnke_at_bov.de
>
> 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: Yechiel Adar
  INET: adar76_at_inter.net.il

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: Stefan Jahnke
  INET: Stefan.Jahnke_at_bov.de

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).