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
我想将一些数据从UAT DB上传到DEV DB。当我尝试从SQL Developer中的导出功能执行此操作时,我收到错误文件C:\ Users \ xxx \ export.sql未打开,因为它超过了最大自动打开大小
How can I copy the UAT data to DEV ?
如何将UAT数据复制到DEV?
ORACLE Version 12C
SQL Developer Version 4.0.0.13
8 个解决方案
#1
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.
似乎“最大自动打开大小”被硬编码为500000(字节,我相信),无法覆盖它。通过限制这一点,我们在试图打开一个巨大的文件时,将Java OutOfMemory的任何潜在投诉扼杀在萌芽状态。
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.
要在SQL Developer中查看文件,尽管存在此限制,只需使用“文件”|“打开”菜单。对于那些巨大的文件,请使用外部编辑器。如果您不想自动打开文件以禁止显示警告对话框,请使用“工具”|“首选项”|“数据库”|“导出/查看DDL选项”,然后取消选中“导出时打开Sql文件”框。
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.
您确定导出文件不包含所有插入行吗?除非你遇到OutOfMemory或磁盘满状态,否则这将是一个错误。我刚刚在55000行表上尝试了你的场景,它生成了一个大约20MB的export.sql。所有行都包括在内。
Regards, Gary Graham SQL Developer Team
此致,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
如果您需要了解一些可以打开大文件的工具,请查看此链接
#2
I was having this error when exporting database in insert format, selecting loader format on the 1st Export wizard screen fixed the issue.
我以插入格式导出数据库时遇到此错误,在1st Export向导屏幕上选择加载器格式修复了问题。
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.
这可能是因为insert格式创建了一个带有DDL和数据作为insert语句的SQL脚本。因此,所有数据库都被转储到单个脚本文件中。
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.
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.
但是,这可能不适用于具有大量数据的单个表,因为该表的数据文件将达到限制。
#3
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.
exp和imp(也是由Oracle)更容易处理,但它们更老,并没有数据泵那么强大。
You might also want to look into the SQL*Plus copy command.
您可能还想查看SQL * Plus复制命令。
#4
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格式导出数据。
#5
Solution 1:
Set these values to some higher value!
将这些值设置为更高的值!
Solution 2:
change "save to" to worksheet!
将“保存到”更改为工作表!
#6
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;
#7
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 - 或者您可以在UAT DB中构建PL / SQL过程以将数据导出为CSV格式的文件,并在DEV DB中使用oracle外部表从该文件中进行SELECT。小心DATE列,使用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;这有点棘手。
#8
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
#1
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.
似乎“最大自动打开大小”被硬编码为500000(字节,我相信),无法覆盖它。通过限制这一点,我们在试图打开一个巨大的文件时,将Java OutOfMemory的任何潜在投诉扼杀在萌芽状态。
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.
要在SQL Developer中查看文件,尽管存在此限制,只需使用“文件”|“打开”菜单。对于那些巨大的文件,请使用外部编辑器。如果您不想自动打开文件以禁止显示警告对话框,请使用“工具”|“首选项”|“数据库”|“导出/查看DDL选项”,然后取消选中“导出时打开Sql文件”框。
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.
您确定导出文件不包含所有插入行吗?除非你遇到OutOfMemory或磁盘满状态,否则这将是一个错误。我刚刚在55000行表上尝试了你的场景,它生成了一个大约20MB的export.sql。所有行都包括在内。
Regards, Gary Graham SQL Developer Team
此致,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
如果您需要了解一些可以打开大文件的工具,请查看此链接
#2
I was having this error when exporting database in insert format, selecting loader format on the 1st Export wizard screen fixed the issue.
我以插入格式导出数据库时遇到此错误,在1st Export向导屏幕上选择加载器格式修复了问题。
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.
这可能是因为insert格式创建了一个带有DDL和数据作为insert语句的SQL脚本。因此,所有数据库都被转储到单个脚本文件中。
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.
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.
但是,这可能不适用于具有大量数据的单个表,因为该表的数据文件将达到限制。
#3
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.
exp和imp(也是由Oracle)更容易处理,但它们更老,并没有数据泵那么强大。
You might also want to look into the SQL*Plus copy command.
您可能还想查看SQL * Plus复制命令。
#4
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格式导出数据。
#5
Solution 1:
Set these values to some higher value!
将这些值设置为更高的值!
Solution 2:
change "save to" to worksheet!
将“保存到”更改为工作表!
#6
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;
#7
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 - 或者您可以在UAT DB中构建PL / SQL过程以将数据导出为CSV格式的文件,并在DEV DB中使用oracle外部表从该文件中进行SELECT。小心DATE列,使用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;这有点棘手。
#8
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