Re: Execute 650 mb sql script

On Sun, 10 Apr 2005 17:53:34 GMT, "IANAL_VISTA" <IANAL_Vista_at_hotmail.com> wrote:

>Paul <paulsnewsgroups_at_hotmail.com> wrote in
>news:g4oi51tni6vnlmf3du3dsu4lktch8g7pes_at_4ax.com:
>
>>
>>
>> bhawin13_at_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

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