oracle 如何将数据从一个数据库/表复制到另一个数据库/表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8944156/
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
提问by name_masked
I have written the following query using the documentation at: Oracle Documentationto copy some data from a database/table on my production server to database/table on Sandbox server.
我使用以下文档编写了以下查询：Oracle 文档将一些数据从我的生产服务器上的数据库/表复制到 Sandbox 服务器上的数据库/表。
COPY FROM username1/passwd1@<production_IP> to username2/passwd2@<sandbox_IP> INSERT TABLE_C (*) USING
(SELECT * FROM TABLE_C WHERE COL_A = 4884);
COPY FROM username1/passwd1@<production_IP> to username2/passwd2@<sandbox_IP> INSERT TABLE_C (*) USING
(SELECT * FROM TABLE_C WHERE COL_A = 4884);
However, I am constantly running into Connection failederror. Is there anything wrong with the query?
但是，我经常遇到Connection failed错误。查询有什么问题吗？
回答by Codo
In a typical Oracle environment, you have TNS names set up. That's a service to lookup the connection parameters for Oracle instances given an SID or service name. In it's simplest form, TNS names is a file called tnsnames.oralocated by the environment variable TNS_ADMIN(which points to the directory where the file is).
在典型的 Oracle 环境中，您设置了 TNS 名称。这是一个服务，用于查找给定 SID 或服务名称的 Oracle 实例的连接参数。在最简单的形式中，TNS 名称是一个tnsnames.ora由环境变量TNS_ADMIN（指向文件所在的目录）定位的文件。
Given the SIDs PRODand SANDBOX, you can then copy the tables from the SQLPLUS command line utility:
给定 SIDPROD和SANDBOX，然后您可以从 SQLPLUS 命令行实用程序复制表：
COPY FROM username1/passwd1@PROD to username2/passwd2@SANDBOX
INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);
Please note that this COPY command only supports a limited set of Oracle datatypes: char, date, long, varchar2, number.
请注意，此 COPY 命令仅支持一组有限的 Oracle 数据类型：char、date、long、varchar2、number。
If you don't have TNS names set up, you'll need to know the host name or IP address, the port number and the service name. The syntax then becomes:
如果您没有设置 TNS 名称，则需要知道主机名或 IP 地址、端口号和服务名称。然后语法变为：
COPY FROM username1/passwd1@//192.168.3.17:1521/PROD_SERVICE to username2/passwd2@//192.168.4.17:1521/SANDBOX_SERVICE
INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);
To determine the SID and/or service name, you best have a look into the TNSNAMES.ORA file on the database server itself. If you are able to login to the database, you can use the following queries to determine the SID and service name (but don't ask me which is which):
要确定 SID 和/或服务名称，您最好查看数据库服务器本身上的 TNSNAMES.ORA 文件。如果您能够登录到数据库，您可以使用以下查询来确定 SID 和服务名称（但不要问我哪个是哪个）：
select name from v$database;
select * from global_name;
select instance_number, instance_name, host_name from v$instance;
回答by susheel
Copy gpl_project/gpl_project@gpldatarto gpl_project/gpl_project@gplrdp. Replace BGROUPMASTERusing select * from BGROUPMASTER.
复制gpl_project/gpl_project@gpldatar到gpl_project/gpl_project@gplrdp. 替换BGROUPMASTER使用select * from BGROUPMASTER.
回答by Steve Trautmann
The following is the solution that I used. I created a link the remote database then used an INSERT command to populate the data.
以下是我使用的解决方案。我创建了远程数据库的链接，然后使用 INSERT 命令填充数据。
CREATE DATABASE LINK database_link_name
CONNECT TO my_user_name IDENTIFIED BY my_password
USING 'tns_name';
INSERT INTO my_table SELECT * FROM my_remote_table@database_link_name;
If you want to get rid of the database link after the work. Use the following:
如果你想在工作后摆脱数据库链接。使用以下内容：
DROP DATABASE LINK database_link_name;
See this link for helpful information: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532217300346683472
有关有用信息，请参阅此链接：https: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 9532217300346683472
