Out of Memory Error : when executing Very Large Scripts in toad

0
votes

I am using

  • Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod &
  • Toad for oracle 10.6.1.3

when i try to run insert statement which is having around 84,000 + records, it is showing Out of Memory error. Here is the error image.

Out of Memory Error

Any of you please suggest me, how i should execute this insert script in toad.

P.S : since toad is connecting to remote machine I'm not able to run it with SQLPLUS. If any one knows option to do that, please let me know.

If you need any more information, Please raise your hands in comment box i will provide you.

2
oracleoracle10gsqlplustoad
This is not a programming question, probably will be closed. Normally you would upload such a big script to the server via FTP/SFTP and then log in via SSH and then run the script with SQLPLUS. - bpgergo
If this script is an insert script that contains multiple insert statements and if it is not an option to upload the script to the server and log in via ssh and run the script with SQLPLUS then I suggest divide and conquer. Split the script into smaller parts in a text editor until you get parts small enough for the TOAD not to throw outofmemory. - bpgergo
okay, whether we can have any query to run this script file in toad?? - Wanna Coffee
committing may also help - bpgergo
Of course you can connect to a remote server using SQL*Plus. After all that's precisely what the tool was built for. You might need to add that to your tnsnames.ora. - a_horse_with_no_name

2 Answers

1
votes

I got the same error when i want to execute sql script 70k rows.

But i solved it just like this.

  • Firstly You should run it with sqlplus commands. Log in the sqlplus and run this command

    @scriptName.sql

  • Secondly (this is alternative) You can use DBLINK in oracle.

0
votes

As bpgergo suggested copy the sql file into remote machine using FTP, then open your SQLPLUS.

Follow this steps in SQLPLUS.

step 1 : change your current session using following query.

alter session set current_schema = Schema_Name;

Here, SCHEMA_NAME is schema name of your insert query table.

step 2 : Execute the sql script file using following query.

@{PATH}/FILE_NAME.SQL

Eg : @D:/oracle/script/FILE_NAME.SQL

Here, D:/oracle/script/ is the file available location and FILE_NAME is your sql script file name.

Now, It will work as expected.