oracle 如何使用toad将数据从一个数据库/表复制到oracle中的另一个数据库/表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18794689/
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 copy data from one database/table to another database/table in oracle using toad
提问by karthick prabhu
I am trying to copy a table data from dev box db to uat db which are 2 different data bases . I am trying in toad.All the connection details are correct but its not working and throwing the following error.
我正在尝试将表数据从 dev box db 复制到 uat db，它们是 2 个不同的数据库。我正在尝试蟾蜍。所有连接细节都是正确的，但它不起作用并抛出以下错误。
[Error] Execution (12: 1): ORA-00900: invalid SQL statement
[错误] 执行 (12: 1): ORA-00900: 无效的 SQL 语句
This is what i am trying
这就是我正在尝试的
copy from abc/cde@//abc.abc.com:1521/devbox to abc/cde@//abc.abc.com/uatbox
INSERT TOOL_SERVICE_MAPPING (*)
USING (SELECT * FROM TOOL_SERVICE_MAPPING)
回答by Andrea
If your table doesn't have a huge number of rows you can use Toad's Export function: it creates an insert statement for each row. You can then run these statements in destination DB to re-create your table's data.
如果您的表没有大量行，您可以使用 Toad 的导出功能：它为每一行创建一个插入语句。然后，您可以在目标数据库中运行这些语句以重新创建表的数据。
Here are the steps:
以下是步骤：
A. Create a copy of the table in destination DB
A. 在目标数据库中创建表的副本
- in source DB in a schema browser window click on the table you want to copy, select "script" tab in the right part of the window: you will find the script to re-create your table; copy this script
- paste the script in a new SQL editor window in destination DB and run it. This should create the new table
- 在模式浏览器窗口的源数据库中，单击要复制的表，在窗口右侧选择“脚本”选项卡：您将找到重新创建表的脚本；复制这个脚本
- 将脚本粘贴到目标数据库的新 SQL 编辑器窗口中并运行它。这应该创建新表
B. Copy data in new table
B. 复制新表中的数据
- in a schema browser window right click on table name in source DB
- select "Export Data" from context menu
- write "where" statement of your export query (leave it blank if you want to copy the entire table)
- select destination: clipboard
- click "ok" (now insert statements are stored in your clipboard)
- paste insert statements in a new SQL editor window in destination DB
- run statements as script (shortcut F5)
- 在模式浏览器窗口中右键单击源数据库中的表名
- 从上下文菜单中选择“导出数据”
- 编写导出查询的“ where”语句（如果要复制整个表，请将其留空）
- 选择目的地：剪贴板
- 单击“确定”（现在插入语句存储在剪贴板中）
- 在目标数据库的新 SQL 编辑器窗口中粘贴插入语句
- 将语句作为脚本运行（快捷键 F5）
回答by Justin Cave
copyis a SQL*Plus command, not a SQL statement. I would be surprised if Toad had implemented that particular SQL*Plus command (it does implement many of the simpler commands). If you want to use the copycommand, you would need to use SQL*Plus, not Toad.
copy是 SQL*Plus 命令，而不是 SQL 语句。如果 Toad 实现了那个特定的 SQL*Plus 命令（它确实实现了许多更简单的命令），我会感到惊讶。如果要使用该copy命令，则需要使用 SQL*Plus，而不是 Toad。
If you want to use Toad, you would need to use a SQL statement to copy the data. You could create a database link in the destination database that points to the source database and then
如果要使用 Toad，则需要使用 SQL 语句来复制数据。您可以在目标数据库中创建一个指向源数据库的数据库链接，然后
INSERT INTO tool_service_mapping
SELECT *
FROM tool_service_mapping@<<db link to source database>>
回答by Peter
The easyest and most error-free way I have experienced so far is: Database->Compare->Schemas
迄今为止我经历过的最简单、最无错误的方法是：Database->Compare->Schemas
It's not too complicated as it looks (lots of checkboxes), but you tick boxes for objects you need to be created in an empty database, and at the end of comparison you end up with SQL script including all objects (triggers, views, sequences, packges) that you selected (checkboxes).
它看起来并不太复杂（很多复选框），但是您勾选需要在空数据库中创建的对象的框，在比较结束时，您最终会得到包含所有对象（触发器、视图、序列）的 SQL 脚本，包）您选择（复选框）。
I clearly see all tables, triggers, data, etc in generated sql script and even can tick these I don't wish to create (if any)... Before executing script, TOAD asks you to confirm against which database you are running the script - saved me few times... As ackward as it looks, it works perfectly.
我清楚地看到生成的 sql 脚本中的所有表、触发器、数据等，甚至可以勾选这些我不想创建的（如果有的话）......在执行脚本之前，TOAD 要求您确认您正在运行哪个数据库脚本 - 救了我几次......尽管看起来很糟糕，但它运行得很好。
I have arround 200 tables I don't know if this is suitable for huge databases.
我有大约 200 个表，我不知道这是否适合大型数据库。
