AW: Looking for help - sql*loader and truncate
Hi Looks like I have to try that one, since truncate should be reasonably faster then replace. Thanks, Stefan -Ursprüngliche Nachricht- Von: Jacques Kilchoer [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 13. November 2003 20:54 An: Multiple recipients of list ORACLE-L Betreff: RE: Looking for help - sql*loader and truncate In that case you could create a procedure owned by the data owner that does the truncate, grant execute on the procedure to the data loader, and use SQL*Plus to call the truncate procedure before the SQL*load starts. > -Original Message- > Stefan Jahnke > > .. and there is another scenario to use replace. As you > mention it, that's > what we do, too ;). The package owner (who owns the > transformation packages) > also does the load, so we use replace here, since I didn't feel like > granting DROP ... to the package user or use the data owner > to do the load. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
AW: Looking for help.
.. and there is another scenario to use replace. As you mention it, that's
what we do, too ;). The package owner (who owns the transformation packages)
also does the load, so we use replace here, since I didn't feel like
granting DROP ... to the package user or use the data owner to do the load.
Stefan
-Ursprüngliche Nachricht-
Von: Rachel Carmichael [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 12. November 2003 18:45
An: Multiple recipients of list ORACLE-L
Betreff: Re: Looking for help.
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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]
> > 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:[EMAIL PROTECTED]
> >
> > 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 [EMAIL PROTECTED] and
> > include the word "subscribe" in either the subject or body.
> >
> > Wednesday, November 12, 2003, 1:07:41 AM,
> > [EMAIL PROTECTED] ([EMAIL PROTECTED]) 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=
> 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 " 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 Genn
AW: Looking for help.
err, should be Win2k, even though it feels like Win0.5k sometimes ;).
Stefan
-Ursprüngliche Nachricht-
Von: Nelson, Allan [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 12. November 2003 14:49
An: Multiple recipients of list ORACLE-L
Betreff: RE: Looking for help.
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:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED] and
include the word "subscribe" in either the subject or body.
Wednesday, November 12, 2003, 1:07:41 AM,
[EMAIL PROTECTED] ([EMAIL PROTECTED]) 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= 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 " 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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 wh
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:[EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
>
> 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 [EMAIL PROTECTED] and
> include the word "subscribe" in either the subject or body.
>
> Wednesday, November 12, 2003, 1:07:41 AM,
> [EMAIL PROTECTED] ([EMAIL PROTECTED]) 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= 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 " 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> Fat City Netwo
AW: Looking for help.
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:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED] and
include the word "subscribe" in either the subject or body.
Wednesday, November 12, 2003, 1:07:41 AM,
[EMAIL PROTECTED] ([EMAIL PROTECTED]) 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= 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 " 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
