Search code examples
sqloracleoracle10gsqlplusdump

Import a large SQL dump via sqlpus

There is a dump in .sql format, to be imported to the server. From tools only sqlplus. The problem is that sqlplus requires that after each CREATE TABLE was / and only after / start creating the table. And dump looks like this:

DROP TABLE ...;
CREATE TABLE ...;
INSERT INTO ...;
...
DROP TABLE ...;
CREATE TABLE ...;
INSERT INTO ...;
...
...

When it comes to INSERT nothing is inserted because the table is not created. Edit the file is not possible because it is a large (~ 700 MB). How to import the dump?

Solution

  • Given the file /tmp/foo.sql:

    CREATE TABLE foo (
    a integer primary key,
    b integer
);

INSERT INTO foo VALUES (1, 2);

    running sqlplus:

    $ sqlplus user/pass@db @/tmp/foo

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 25 10:54:39 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.4.0 - 64bit Production
JServer Release 9.2.0.4.0 - Production


Table created.


1 row created.

SQL>

    It sounds like you've set delimiter to / somewhere. Change it back to ;.

    If you need to edit the file, you can do so with perl -p -E "s/pattern/replacement/" oldfile.sql > newfile.sql or similar. (Or, honestly, many editors can handle 700MB files on modern machines with many gigabytes of RAM).