Forum Stats

  • 3,874,176 Users
  • 2,266,677 Discussions
  • 7,911,757 Comments

Discussions

Run large data export script

Earl Lewis
Earl Lewis Member Posts: 2,170 Silver Badge

I'm having trouble running a large schema export script (~.5M lines), generated with SQL Developer Data Export. I'm trying to move a schema from one instance to another and don't have access to data pump on the source side so can't use that.

I've tried to run the script in SQL Developer and it just hangs after about 10 commands in. And also tried running it as a script file in SQLcl. The weird part of the SQLcl run is that I can see that the views got created, but almost nothing else (no packages, procedures, functions, tables).

I added "set termout off" to the top of the file before I ran it, so hopefully it would help on the I/O side of things but somewhere along the way this failed (apparently) and not sure why, or how to fix it, besides trying to break the script up into smaller chunks, which I'm loathe to do.

Any suggestions for running these really large export scripts in SQLcl?

Tagged:

Best Answer

  • Earl Lewis
    Earl Lewis Member Posts: 2,170 Silver Badge
    Answer ✓

    I was able to make this work by dividing up the schema export scripts via SQL Developer. The DDL went in one script then I had to break the DML up into several separate scripts. Seems like the key to making it work was putting a commit into the data exports for every 1000 rows or so. Not the most elegant solution, but the tools do provide the means to make this happen pretty easily, so thanks again to the SQL Developer and SQLcl teams for all the great tooling!

Answers

  • Justin Warwick
    Justin Warwick Member Posts: 123 Bronze Badge

    A couple of things that might help troubleshoot: what version of SQL Developer are you running? And what about the client OS/platform?

    How many total objects in that large schema? I'm guessing that most of those 500,000 lines are DML (for the actual data in the tables).

    Have you tried executing with Autotrace enabled? It should be available in sqlcl and SQL Developer. Perhaps it will yield up a little more information about where/how the hang is happening.

  • Earl Lewis
    Earl Lewis Member Posts: 2,170 Silver Badge

    The client OS for the database is an Oracle Linux VM on VirtualBox (one of the Developer Days VMs, with 19C running. I have to do my development off the development platform for reasons I won't go into here, so I'm doing it "locally").

    I'm running the VM in headless mode and connecting to it from my Mac OSX installation of SQL Developer and SQLcl. The VM runs pretty laggy when using the UI so this keeps the VM UI out of the mix and gives pretty good performance for SQL Developer/SQLcl. The SQL Developer version is 20.4.0.379.

    The schema is not that big (500 objects?) and yes, the bulk of the script is DML/data. I have not tried autotrace enabled.

  • Justin Warwick
    Justin Warwick Member Posts: 123 Bronze Badge

    My setup does not match, so for whatever it is worth:

    I am using  SQLdev v20.4.1.407 on Windows 10 connected to an 18c XE DB, and I successfully created a single test table (only 3 columns) and populated it with over a million rows, then used the SQL Dev export wizard with defaults, other than to use DML to capture data, into a single SQL file; then truncate the table and execute the SQL file. There were no errors and no hangs.

    The only other things I can think of is some kind of NLS setting interfering.

    It might be worth creating a copy of the file and trimming it down to just a few lines of each type to see if that generates some clues.

  • Earl Lewis
    Earl Lewis Member Posts: 2,170 Silver Badge
    Answer ✓

    I was able to make this work by dividing up the schema export scripts via SQL Developer. The DDL went in one script then I had to break the DML up into several separate scripts. Seems like the key to making it work was putting a commit into the data exports for every 1000 rows or so. Not the most elegant solution, but the tools do provide the means to make this happen pretty easily, so thanks again to the SQL Developer and SQLcl teams for all the great tooling!