Importing 3954275 Insert statements into Oracle 10g

Question

How do i import a script with 3954275 Lines of Insert Statements into a Oracle 10g. I can do it with sqlplus user/pass @ script.sql but this is dam slow (even worse the commit is at the end of this 900MB file. I dont know if my Oracle configuration can handle this). Is there a better (faster) way to import the Data? Btw. the DB is empty before the Import.

Answer 1

Use SQL*Loader.

It can parse even your INSERT commands if you don't have your data in another format.

Answer 2

Agreed with the above: use SQL*Loader.

However, if that is not an option, you can adjust the size of the blocks that SQL Plus brings in by putting the statement

SET arraysize 1000;

at the beginning of your script. This is just an example from my own scripts, and you may have to fine tune it to your needs considering latency, etc. I think it defaults to like 15, so you're getting a lot of overhead in your script.

Answer 3

SQL*Loader is a good alternative if your 900MB file contains insert statements to the same table. It will be cumbersome if it contains numerous tables. It is the fastest option however.

If for some reason a little improvement is good enough, then make sure your sessions CURSOR SHARING parameter is set to FORCE or SIMILAR. Each insert statement in your file will likely be the same except for the values. If CURSOR_SHARING is set to EXACT, then each of insert statements needs to be hard parsed, because it is unique. FORCE and SIMILAR automatically turns your literals in the VALUES clause to bind variables, removing the need for the hard parse over and over again.

You can use the script below to test this:

set echo on
alter system flush shared_pool
/
create table t
( id   int
, name varchar2(30)
)
/
set echo off
set feedback off
set heading off
set termout off
spool sof11.txt
prompt begin
 select 'insert into t (id,name) values (' || to_char(level) || ', ''name' || to_char(level) || ''');'
   from dual
connect by level <= 10000
/
prompt end;;
prompt /
spool off
set termout on
set heading on
set feedback on
set timing on
@sof11.txt
set timing off
alter session set cursor_sharing = force
/
set timing on
@sof11.txt
set timing off
alter session set cursor_sharing = exact
/
set echo off
drop table t purge
/

The example executes 10,000 statements like "insert into t (id,name) values (1, 'name1'); ". The output on my laptop:

SQL> alter system flush shared_pool
  2  /

Systeem is gewijzigd.

SQL> create table t
  2  ( id   int
  3  , name varchar2(30)
  4  )
  5  /

Tabel is aangemaakt.

SQL> set echo off

PL/SQL-procedure is geslaagd.

Verstreken: 00:00:17.10

Sessie is gewijzigd.


PL/SQL-procedure is geslaagd.

Verstreken: 00:00:05.50

More than 3 times as fast with CURSOR_SHARING set to FORCE.

Hope this helps.

Regards, Rob.