Import 3954275 Insert Claims in Oracle 10g

Question

How to import a script with 3954275 insert lines in Oracle 10g. I can do this with help sqlplus user/pass @ script.sql, but this dam is slow (even worse, the commit is at the end of this 900 MB file. I don't know if my Oracle configuration can handle this). Is there a better (faster) way to import data? Btw. The database is empty before import.

Answer 1

Use SQL*Loader.

It can even analyze your commands INSERTif you do not have data in a different format.

Answer 2

SQL * Loader - , 900MB . , . .

- , , CURSOR SHARING FORCE SIMILAR. insert , , , . CURSOR_SHARING EXACT, , . FORCE SIMILAR VALUES , .

script , :

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
/

10 000 , "insert into t (id, name) values ​​(1, 'name1');" :

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

3 , CURSOR_SHARING, FORCE.

, .

, .

Answer 3

Consistent with the above: use SQL * Loader .

However, if this is not an option, you can adjust the size of the blocks that SQL Plus enters by setting the statement

SET arraysize 1000;

at the beginning of your script. This is just an example from my own scenarios, and you might have to customize it to your needs, taking into account the delay, etc. I think the default is 15, so you get a lot of overhead in the script.