Execute 650 mb sql script

[Guest]

Hello,
I have 650 mb sql script. how to run such big size script sucessfully.
I have 1 gb ram.

I have tried it with sql*plus, toad and object browser. But during
loading script all hangs and at last it gives memory error.

Is there anyway to run such large script?

Thanks,
bhawin13

[Paul]

bhawin13@indiatimes.com wrote:

I have 650 mb sql script. how to run such big size script sucessfully.I have 1 gb ram.


I'm not sure whether this will work or not, but when I was doing a
project, I put the scripts for all the different tables into different
files and then launched a "mother-script" which went through all of
the different files (with regular COMMITS) - maybe this might work?

How often do you COMMIT during the script? If never, then this *_may_*
be the problem. I would *_assume_* that most of the script is of the
nature of INSERT INTO TableBlah VALUES (blah1, blah2..... blahn).


Try putting "COMMIT;" at the end of every block of 5000 or so records?
Or even at the end of each completed table entry?

Better still if you figure out how to do this programatically - I'm
not familiar enough yet with the Oracle tools to be able to give you
an answer to this.



Paul...

bhawin13


--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system and version of db being used.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

[IANAL_VISTA]

Paul <paulsnewsgroups@hotmail.com> wrote in
news:g4oi51tni6vnlmf3du3dsu4lktch8g7pes@4ax.com:
bhawin13@indiatimes.com wrote:I have 650 mb sql script. how to run such big size script sucessfully.I have 1 gb ram. I'm not sure whether this will work or not, but when I was doing a project, I put the scripts for all the different tables into different files and then launched a "mother-script" which went through all of the different files (with regular COMMITS) - maybe this might work? How often do you COMMIT during the script? If never, then this *_may_* be the problem. I would *_assume_* that most of the script is of the nature of INSERT INTO TableBlah VALUES (blah1, blah2..... blahn). Try putting "COMMIT;" at the end of every block of 5000 or so records? Or even at the end of each completed table entry?

Intervening COMMITs may increase the chances for ORA-01555 error;
and consumes more resources.

[Daniel Morgan]

Paul wrote:
bhawin13@indiatimes.com wrote:I have 650 mb sql script. how to run such big size script sucessfully.I have 1 gb ram. I'm not sure whether this will work or not, but when I was doing a project, I put the scripts for all the different tables into different files and then launched a "mother-script" which went through all of the different files (with regular COMMITS) - maybe this might work? How often do you COMMIT during the script? If never, then this *_may_* be the problem. I would *_assume_* that most of the script is of the nature of INSERT INTO TableBlah VALUES (blah1, blah2..... blahn). Try putting "COMMIT;" at the end of every block of 5000 or so records? Or even at the end of each completed table entry? Better still if you figure out how to do this programatically - I'm not familiar enough yet with the Oracle tools to be able to give you an answer to this. Paul...bhawin13

As IANAL_VISTA indicates ... a really bad idea.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Daniel Morgan]

bhawin13@indiatimes.com wrote:
Hello, I have 650 mb sql script. how to run such big size script sucessfully. I have 1 gb ram. I have tried it with sql*plus, toad and object browser. But during loading script all hangs and at last it gives memory error. Is there anyway to run such large script? Thanks, bhawin13

No Oracle version.
No hardware information except RAM
No operating system information
No information on SGA size
No explanation as to what the script does or how it could possibly be so
large.
No actual error message to consider
No crystal ball, taro cards, or lucky rabbits foot
No help possible
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Lewis C]

On Sun, 10 Apr 2005 17:53:34 GMT, "IANAL_VISTA"
<IANAL_Vista@hotmail.com> wrote:
Paul <paulsnewsgroups@hotmail.com> wrote innews:g4oi51tni6vnlmf3du3dsu4lktch8g7pes@4ax.com: bhawin13@indiatimes.com wrote:I have 650 mb sql script. how to run such big size script sucessfully.I have 1 gb ram. Try putting "COMMIT;" at the end of every block of 5000 or so records? Or even at the end of each completed table entry?Intervening COMMITs may increase the chances for ORA-01555 error;and consumes more resources.

The ora-01555 won't occur if Paul's supposition that it's a script
full of INSERT INTO TABLES VALUES () statements is true. And
depending on space allocation, commits may be required. If he's doing
a bunch of INSERT SELECTs, commits will be a problem. Although if
they're INSERT SELECTS, I can't imagine why the file is 650megs.

By far, the place I most often see ORA-01555 is commiting in a cursor
for loop. In that case, you're very much correct. If the rollback or
undo is properly sized, there should be no reason for comitting in a
cursor loop.

But, as someone else said, without more information, it's just a
guessing game.

Thanks,

Lewis




-----------------------------------------------------------
Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle
http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database
http://www.suite101.com/welcome.cfm/oracle

Sign up for courses here:
http://www.suite101.com/suiteu/default.cfm/416752
-----------------------------------------------------------

[Paul]

DA Morgan <damorgan@x.washington.edu> wrote:
Paul wrote:
COMMIT regularly
As IANAL_VISTA indicates ... a really bad idea.


Could this suggestion (based as you point out on minimum info) be
valid in *_some_* circumstances?


Paul...



--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system and version of db being used.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

[Daniel Morgan]

Paul wrote:
DA Morgan <damorgan@x.washington.edu> wrote:Paul wrote:COMMIT regularlyAs IANAL_VISTA indicates ... a really bad idea. Could this suggestion (based as you point out on minimum info) be valid in *_some_* circumstances? Paul...

Not in my experience and not based on my reading of what Tom Kyte
has written on the subject numerous times.

Which of course doesn't mean that there isn't some situation where
it is true but I would expect that to be, for the most part,
contrived. In Oracle it is best to stay away from absolutes because
someone will absolutely prove you wrong.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Frank van Bortel]

Paul wrote: DA Morgan <damorgan@x.washington.edu> wrote:Paul wrote:COMMIT regularlyAs IANAL_VISTA indicates ... a really bad idea. Could this suggestion (based as you point out on minimum info) be valid in *_some_* circumstances? Paul...

Yes. Break up the 650MB file in small chunks.
And and insert into table(...) values (...); will succeed, as long
as that piece of code remains smaller than 32kB
(or larger - latest version, which is absent again...)
<rant>
Really appreciate the feedback, bhawin13.
BTW, who named you that?
</rant>

--
Regards,
Frank van Bortel

[Daniel Morgan]

Frank van Bortel wrote:
Yes. Break up the 650MB file in small chunks. And and insert into table(...) values (...); will succeed, as long as that piece of code remains smaller than 32kB (or larger - latest version, which is absent again...)

How can you give this advice when you have no more idea than the
rest of us, I presume, as to what is in the script?

Did I miss something in the thread?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Frank van Bortel]

DA Morgan wrote: Frank van Bortel wrote: Yes. Break up the 650MB file in small chunks. And and insert into table(...) values (...); will succeed, as long as that piece of code remains smaller than 32kB (or larger - latest version, which is absent again...) How can you give this advice when you have no more idea than the rest of us, I presume, as to what is in the script? Did I miss something in the thread?

Yes [, Paul's suggestion could be valid in *some* circumstances].

[If all you need to do is] break up the 650MB file is chunks.

The above two lines was mainly what I was reacting upon.

Oracle Designer product had this nice widget, called WebServer
Generator (WSG) - it happily generated chunks > 32kByte, and
I have suggested more than once to edit the file, and break it
up in chunks. (or use server manager, as that used a larger
buffer... not valid in this situation, 650MB is really too big)

And I have managed to generate an Offline Instantiation file
for Advanced Replication of about 800 MB (eight hundred, yes).
DVD burners had yet to be invented, then :(

So, I've been there, done some. And the suggestion that frequent
commits e.g. every 5000 inserts, will cause ORA-1555 is simply
not true *when 5000 inserts equals 5000 lines of code*
There is overhead, sure, but "insert into ....; commit;" will
not generate a snapshot too old.

For clarity: commit over a cursor (a.k.a. committing within a
loop) will cause ORA-1555's.
--
Regards,
Frank van Bortel

[Daniel Morgan]

Frank van Bortel wrote:
DA Morgan wrote:Frank van Bortel wrote:Yes. Break up the 650MB file in small chunks.And and insert into table(...) values (...); will succeed, as longas that piece of code remains smaller than 32kB(or larger - latest version, which is absent again...)How can you give this advice when you have no more idea than therest of us, I presume, as to what is in the script?Did I miss something in the thread? Yes [, Paul's suggestion could be valid in *some* circumstances]. [If all you need to do is] break up the 650MB file is chunks. The above two lines was mainly what I was reacting upon.

Then it makes sense. I kept thinking what could you possibly put
into a file, to insert into Oracle, that would be 650MB? If it
was a .dmp file for import I could buy it. If it was a .dat file
for SQL*Loader I could buy it. But 650MB of inserts? Someone needs
to be taken out to the woodshed.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[IANAL_VISTA]

Frank van Bortel <fvanbortel@netscape.net> wrote in
news:d3ed8e$q7a$1@news6.zwoll1.ov.home.nl: For clarity: commit over a cursor (a.k.a. committing within a loop) will cause ORA-1555's.

1) The above statement is not true in all cases.

s/will/can/


2) In my post I purposefully said "may" cause ORA-01555.

As usual, it depends.

The OP seems to the that Usenet is a write once medium.

[Frank van Bortel]

IANAL_VISTA wrote: Frank van Bortel <fvanbortel@netscape.net> wrote in news:d3ed8e$q7a$1@news6.zwoll1.ov.home.nl:For clarity: commit over a cursor (a.k.a. committing within aloop) will cause ORA-1555's. 1) The above statement is not true in all cases. s/will/can/

I was expecting that :)
And no - I will kepp on stating that it will.
If it doesn't, consider yourself lucky.
2) In my post I purposefully said "may" cause ORA-01555.

Paul said:
be the problem. I would *_assume_* that most of the script is of the nature of INSERT INTO TableBlah VALUES (blah1, blah2..... blahn). Try putting "COMMIT;" at the end of every block of 5000 or so records? Or even at the end of each completed table entry?

Your reply:
Intervening COMMITs may increase the chances for ORA-01555 error; and consumes more resources.

Please explain when you perceive an ORA-1555 when having
thousands of lines like
INSERT INTO TableBlah VALUES (blah1, blah2..... blahn)

It's not going to happen.
--
Regards,
Frank van Bortel