Or... Create a stored procedure that truncates the table... Grant execute
on the procedure to the user... The user executes the procedure and then
calls sqlldr...
Tim
-----Original Message-----
Sent: Wednesday, November 12, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L
yeah but...
if you attempt (as I do) to isolate the schema owner from the users which
have select/insert/update/delete privileges, TRUNCATE won't work unless you
have granted DROP ANY TABLE (I *really* hate that that is
required) to the account which does the actual sql load. So I use REPLACE,
because then I only have to grant the delete priv on that table.
- Yechiel Adar <adar76_at_inter.net.il> wrote:
> 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).
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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: Johnston, Tim
INET: TJohnston_at_quallaby.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).
Received on Wed Nov 12 2003 - 12:29:41 CST
