如何使用sql developer导出大量数据 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30091567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export large amount of data using sql developer - Oracle
提问by ever alian
I want to upload some data from UAT DBto 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
我想从UAT DBto上传一些数据DEV DB。当我尝试从导出函数执行此操作时SQL Developer，出现错误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 ?
如何将 UAT 数据复制到 DEV ？
ORACLE Version 12C
SQL Developer Version 4.0.0.13
回答by Nomesh DeSilva
found the below answer from a SQL Developer forum :
从 SQL Developer 论坛找到以下答案：
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
似乎“最大自动打开大小”被硬编码为 500000（字节，我相信）的值，无法覆盖它。通过限制这一点，我们在尝试打开一个大文件时将 Java OutOfMemory 的任何潜在抱怨扼杀在萌芽状态。
尽管有此限制，但要从 SQL Developer 中查看文件，只需使用 File|Open 菜单。对于那些大文件，请使用外部编辑器。如果您不想自动打开文件以取消警告对话框，请使用工具|首选项|数据库|导出/查看 DDL 选项并取消选中“导出时打开 Sql 文件”框。
您确定导出文件不包含所有插入行吗？除非您遇到 OutOfMemory 或磁盘已满的情况，否则这将是一个错误。我刚刚在 55000 行表上尝试了您的方案，该表产生了大约 20MB 的 export.sql。包括所有行。
此致， Gary Graham SQL 开发团队
and as the summary, it suggested that the SQL developer is not the best tool to open a large size of data file.
作为总结，它表明 SQL 开发人员不是打开大型数据文件的最佳工具。
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
如果您需要了解一些可以打开大文件的工具，请查看此链接
回答by Kshitiz Sharma
I was having this error when exporting database in insertformat, selecting loader format on the 1st Export wizardscreen fixed the issue.
以insert格式导出数据库时出现此错误，在第一个Export wizard屏幕上选择加载程序格式修复了该问题。
This is probably because insertformat creates a single SQL script with DDL and data as insert statements. So all the database is dumped in a single script file.
这可能是因为insertformat 使用 DDL 和数据作为插入语句创建了单个 SQL 脚本。因此，所有数据库都转储到一个脚本文件中。
loaderoption 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.
loader选项生成多个文件：控制文件、数据文件和 sql 文件。每个表都有单独的文件。因此，导出将包含数百个文件，并且没有一个文件会达到大小限制。
This may not however work with single tables with very large amounts of data as that table's data file would hit the limit.
然而，这可能不适用于具有大量数据的单个表，因为该表的数据文件会达到限制。
回答by user7023213
Solution 1:
解决方案1：
Set these values to some higher value!
将这些值设置为更高的值！
Solution 2:
解决方案2：
change "save to" to worksheet!
将“保存到”更改为工作表！
回答by Mathavaprakash
You can use spool the query and save the results as CSV or XLSX files for larger results. For example:
您可以使用假脱机查询并将结果保存为 CSV 或 XLSX 文件以获得更大的结果。例如：
spool "D:\Temp\Report.csv"
SELECT /*csv*/ select id,name,age from EMP;
spool off;
回答by san242
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.
在 SQL 开发人员中，当右键单击表并单击导出时，将启动导出向导，您可以选择“另存为”-“单独的文件”，将数据导出到同一 SQL 文件中。或者，您可以将同一向导上的格式类型更改为 CSV，以便以 CSV 格式导出数据。
回答by René Nyffenegger
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.
首先，看看数据泵。不过，它有一点学习曲线。
expand imp(also by Oracle) are a bit easier to handle, but they're older and not nearly as powerful as data pump.
exp和imp（也由 Oracle 提供）更容易处理，但它们更旧，而且不如数据泵强大。
You might also want to look into the SQL*Plus copy command.
您可能还想查看SQL*Plus 复制命令。
回答by Ramesh Pradhan
There is a trick to copy large chunk of data (from SQL developer) into excel sheet.
有一个技巧可以将大块数据（来自 SQL 开发人员）复制到 Excel 表中。
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 :)
要遵循的步骤：右键单击 ---> 导出数据 ----> 选择格式类型为“文本” ---> 选择类型为“剪贴板” ----> 打开一个 Excel 工作表并尝试粘贴保持下面记住:)
Then paste the data NOTE : **Do Not paste the data on the first cell of the excel. Ctrl+v in any of the columns **
然后粘贴数据注意：**不要将数据粘贴在excel的第一个单元格上。任何列中的 Ctrl+v **
This will work.
这将起作用。
Thanks
谢谢
回答by alvalongo
1-You can create a database link (db link) on DEV DB pointing to UAT DB, to INSERT rows in DEV DB.
1-您可以在 DEV DB 上创建一个指向 UAT DB 的数据库链接（db link），以插入 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.
2-或者您可以在 PL/SQL 中构建 UAT DB 中的一个过程，以将数据导出到 CSV 格式的文件中，并在 DEV DB 中使用 oracle 外部表从该文件中进行 SELECT。
注意 DATE acolumns，使用 TO_CHAR 记下。
3-Use Datapump to export data from UAT DB and then import into DEV DB; it's a bit tricky.
3-使用Datapump从UAT DB导出数据，然后导入DEV DB；这有点棘手。
