如何将数据从一个数据库/表复制到另一个数据库/表 [英] 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@到 username2/passwd2@插入 TABLE_C (*) 使用(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屋！