How to export large amount of data using sql developer - Oracle

E

11

17

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

Exacting answered 7/5, 2015 at 3:48 Comment(2)

Are you sure you want to do it with SQL developer? There are better tools more suited for such a task. – Nanosecond 7/5, 2015 at 5:21

@RenéNyffenegger what are the tools available? – Exacting 7/5, 2015 at 5:35

H

10

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

Harville answered 7/5, 2015 at 4:2 Comment(3)

Before I open the sql file, I want to take the data out from the table. This is where I have the error now. – Exacting 7/5, 2015 at 5:37

why don't you query data set by set and export if that is the case? – Harville 7/5, 2015 at 6:42

With the same version of SQL Developer, I get the same error, but the file is correctly exported (the exported SQL file is 265MB). Thus I assume that the error occurs when SQL Developer tries to open the exported file. But you can live without opening the exported file if your goal is just to export the data => ignore the error ! – Murk 6/5, 2022 at 16:1

A

5

Solution 1:

Set these values to some higher value!

Solution 2:

change "save to" to worksheet!

Aymara answered 16/1, 2019 at 14:36 Comment(0)

U

4

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.

Unfrequented answered 26/6, 2015 at 13:5 Comment(2)

Just because I searched for this

"saving as a single file is not supported for exporting data that is formatted as loader with generate separate data file"

and google provided no results, I'm leaving it here as your answer helped me as I didnt realize loader needed to be multiple files! Thanks – Omega 15/10, 2019 at 22:23

im currently trying out the loader option on a database that has less than 1GB of data in a few 100 tables. So far it has created 188K files and has exported 211k rows so far. Looks pretty weird to me, i miss the easy means of ex- and importing im used from MySQL and Postgres :( Files created look like this: AO_6FF49D_MIGRATION_ENTITY_DATA_TABLE543bf9e0-017f-1000-855e-0ae905308984.ldr AO_6FF49D_MIGRATION_ENTITY_DATA_TABLE543bf9e0-017f-1000-855e-0ae905308984.ldr ... – Daytoday 4/3, 2022 at 9:48

C

2

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.

Caen answered 21/4, 2016 at 16:26 Comment(0)

T

1

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.

Tomikotomkiel answered 7/5, 2015 at 5:43 Comment(0)

D

1

You can use spool the query and save the results as CSV or XLSX files for larger results. For example:

spool "D:\Temp\Report.csv"
SELECT /*csv*/ select id,name,age from EMP;
spool off;

Dittography answered 9/4, 2019 at 16:26 Comment(0)

R

1

Simplest way to this is to modify the "Save As" below in the screenshot to save to multiple files instead of single file while exporting-

Rahel answered 12/9, 2022 at 8:2 Comment(1)

This should be the answer. It's simple to do & it works. :) – Playa 1/10, 2023 at 1:25

H

0

There is a trick to copy large chunk of data (from SQL developer) into excel sheet.

steps to be followed : Right click ---> export data ----> select format type as 'Text' ---> select type as "Clipboard" ----> open an excel sheet and try to paste keeping the below in mind :)

Then paste the data NOTE : **Do Not paste the data on the first cell of the excel. Ctrl+v in any of the columns **

This will work.

Thanks

Horizon answered 1/6, 2017 at 11:27 Comment(1)

Clipboard is definitely not the way to transfer large amounts of data. If OP is hitting out of memory errors then Clipboard is not going to help you because it is memory constrained. – Caitlin 16/5, 2018 at 14:41

A

0

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.

Allegraallegretto answered 9/4, 2019 at 19:44 Comment(0)

J

0

Oracle database commands can run both in SqlCl by Oracle and in SQL developer, so this is easy:

set feedback only -- for Oracle 12.2+, turn off terminal output
set sqlformat insert -- data in "insert into ..." format
-- set sqlformat csv -- data in csv format
spool /path/to/your/file.sql
select * from t; -- lines to export
spool off
set feedback off  -- restore terminal output

Recommended topics

Hot tags