How to export large amount of data using sql devel

Question

I want to upload some data from UAT DB to DEV DB. When I try to do this from Export function in SQL Developer, I got an error File C:\Users\xxx\export.sql was not opened because it exceeds the maximum automatic open size

How can I copy the UAT data to DEV ?

ORACLE Version 12C
SQL Developer Version 4.0.0.13

Answer 1

found the below answer from a SQL Developer forum :

It appears that the "maximum automatic open size" is hard-coded to a value of 500000 (bytes, I believe) with no way to override it. By limiting this, we nip in the bud any potential complaints of Java OutOfMemory upon trying to open a huge file.

To view the file from within SQL Developer despite this limitation, just use the File|Open menu. For those huge files, please use an external editor. And if you don't want to open files automatically in order to suppress the warning dialog, use Tools|Preferences|Database|Export/View DDL Options and un-check the "Open Sql File When Exported" box.

Are you certain the export file does not contain all the insert rows? That would be a bug unless you hit an OutOfMemory or disk full condition. I just tried your scenario on at 55000 row table that produced an export.sql of about 20MB. All rows were included.

Regards, Gary Graham SQL Developer Team

and as the summary, it suggested that the SQL developer is not the best tool to open a large size of data file.

hope Gary's answer will guide you to some extent.

If you need to get an idea of some tools that you can open large files, check this LINK

Answer 2

I was having this error when exporting database in insert format, selecting loader format on the 1st Export wizard screen fixed the issue.

This is probably because insert format creates a single SQL script with DDL and data as insert statements. So all the database is dumped in a single script file.

loader option produces multiple files: control file, data file, and sql files. And there are separate files for each table. As a result the export will consist of hundreds of files and no one file will reach the size limit.

This may not however work with single tables with very large amounts of data as that table's data file would hit the limit.

Answer 3

You can try different options like below.

On SQL developer, when right click on Table and click export, export wizard will be launched you can select either "Save As" - "separate files" that will export data in same SQL file. OR you can change the format type on the same wizard to CSV that will export data in CSV format.

Answer 4

Solution 1:

Set these values to some higher value!

Solution 2:

change "save to" to worksheet!

Answer 5

If you want to transfer large amounts of data (or small amounts, too) from one database to another, you should consider the tools that were specifically designed for such tasks.

First and foremost, look into data pump. It has a bit of a learning curve, though.

exp and imp (also by Oracle) are a bit easier to handle, but they're older and not nearly as powerful as data pump.

You might also want to look into the SQL*Plus copy command.

Answer 6

1-You can create a database link (db link) on DEV DB pointing to UAT DB, to INSERT rows in DEV DB.

2-Or you can build in PL/SQL a procedure in UAT DB to export data to a file in CSV format and in DEV DB use oracle external tables to SELECT from that files.
Be carefull about DATE acolumns, write down using TO_CHAR.

3-Use Datapump to export data from UAT DB and then import into DEV DB; it's a bit tricky.