Re: Looking for help.

That's the target year for the stable and secure release of MS Windows. HAL:What are you doing Dave? David Bowman: I'm turning you off, HAL.

On 11/12/2003 08:49:26 AM, "Nelson, Allan" wrote:
> Windows 3000?
>
> -----Original Message-----
> Sent: Wednesday, November 12, 2003 7:44 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).
>
>
> ______________________________________________________________________________
> This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Nelson, Allan
> INET: anelson_at_midf.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).
>

Mladen Gogala
Oracle DBA

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).