如何将数据从一个数据库/表复制到另一数据库/表 [英] How to copy data from one database/table to another database/table

问题描述

我使用以下文档提供了以下查询: Oracle文档可以将某些数据从生产服务器上的数据库/表复制到沙盒服务器上的数据库/表.

I have written the following query using the documentation at: Oracle Documentation to copy some data from a database/table on my production server to database/table on Sandbox server.

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);

但是，我经常遇到Connection failed错误.查询有什么问题吗?

However, I am constantly running into Connection failed error. Is there anything wrong with the query?

推荐答案

在典型的Oracle环境中，您需要设置TNS名称.该服务用于在给定SID或服务名称的情况下查找Oracle实例的连接参数.用最简单的形式，TNS名称是一个名为tnsnames.ora的文件，位于环境变量TNS_ADMIN的位置(该文件指向该文件所在的目录).

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.ora located by the environment variable TNS_ADMIN (which points to the directory where the file is).

鉴于SID PROD和SANDBOX，您可以从SQLPLUS命令行实用程序复制表:

Given the SIDs PROD and SANDBOX, you can then copy the tables from the SQLPLUS command line utility:

COPY FROM username1/passwd1@PROD to username2/passwd2@SANDBOX
    INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);

请注意，此COPY命令仅支持一组有限的Oracle数据类型:char，date，long，varchar2，number.

Please note that this COPY command only supports a limited set of Oracle datatypes: char, date, long, varchar2, number.

如果未设置TNS名称，则需要知道主机名或IP地址，端口号和服务名称.语法变为:

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:

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);

要确定SID和/或服务名称，最好查看数据库服务器本身上的TNSNAMES.ORA文件.如果您能够登录数据库，则可以使用以下查询来确定SID和服务名称(但不要问我是哪个):

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):

select name from v$database;

select * from global_name;

select instance_number, instance_name, host_name from v$instance;

这篇关于如何将数据从一个数据库/表复制到另一数据库/表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！