I have 2 DB with the same schema on different servers. I need to copy data from table T to the same table T in test database in different server and network.
我有两个DB，在不同的服务器上有相同的模式。我需要将数据从表T复制到不同服务器和网络的测试数据库中的同一个表T。
What is the easiest way to do it? I heard that data can be dumped to flat file and than inserted into database. How does it works? Can this be achieved using sqlplus and oracle database?
最简单的方法是什么?我听说数据可以转储到平面文件中，而不是插入到数据库中。它是如何运作的吗?可以使用sqlplus和oracle数据库实现这一点吗?
Thank you!
谢谢你！
1 个解决方案
#1
3
Use Oracle export to export a whole table to a file, copy the file to serverB and import.
使用Oracle export将整个表导出到文件，将文件复制到serverB并导入。
http://www.orafaq.com/wiki/Import_Export_FAQ
You can use rsync to sync an oracle .dbf file or files to another server. This has problems and syncing all files works more reliably.
可以使用rsync将oracle .dbf文件或文件同步到另一个服务器。这有问题，同步所有文件的工作更可靠。
For groups of records, write a query to build a pipe-delimited (or whatever delimiter suits your data) file with rows you need to move. Copy that file to serverB. Write a control file for sqlldr and use sqlldr to load the rows into the table. sqlldr is part of the oracle installation.
对于记录组，编写一个查询，用需要移动的行构建管道分隔符(或任何适合数据的分隔符)文件。将该文件复制到serverB中。为sqlldr编写一个控制文件，并使用sqlldr将行加载到表中。sqlldr是oracle安装的一部分。
http://www.thegeekstuff.com/2012/06/oracle-sqlldr/
If you have db listeners up on each server and tnsnames knows about both, you can directly:
如果在每个服务器上都有db监听器，而tnsnames对这两个都知道，您可以直接:
insert into mytable@remote
select * from mytable
where somecolumn=somevalue;
Look at the remote table section:
查看远程表部分:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm
If this is going to be an ongoing thing, create a db link from instance@serverA to instance@serverB. You can then do anything you have permissions for with data on one instance or the other or both.
如果这是一个正在进行的事情，那么创建一个从instance@serverA到instance@serverB的db链接。然后，您可以对一个实例或另一个实例上的数据或两者上的数据进行任何有权限的操作。
http://psoug.org/definition/CREATE_DATABASE_LINK.htm
#1
3
Use Oracle export to export a whole table to a file, copy the file to serverB and import.
使用Oracle export将整个表导出到文件，将文件复制到serverB并导入。
http://www.orafaq.com/wiki/Import_Export_FAQ
You can use rsync to sync an oracle .dbf file or files to another server. This has problems and syncing all files works more reliably.
可以使用rsync将oracle .dbf文件或文件同步到另一个服务器。这有问题，同步所有文件的工作更可靠。
For groups of records, write a query to build a pipe-delimited (or whatever delimiter suits your data) file with rows you need to move. Copy that file to serverB. Write a control file for sqlldr and use sqlldr to load the rows into the table. sqlldr is part of the oracle installation.
对于记录组，编写一个查询，用需要移动的行构建管道分隔符(或任何适合数据的分隔符)文件。将该文件复制到serverB中。为sqlldr编写一个控制文件，并使用sqlldr将行加载到表中。sqlldr是oracle安装的一部分。
http://www.thegeekstuff.com/2012/06/oracle-sqlldr/
If you have db listeners up on each server and tnsnames knows about both, you can directly:
如果在每个服务器上都有db监听器，而tnsnames对这两个都知道，您可以直接:
insert into mytable@remote
select * from mytable
where somecolumn=somevalue;
Look at the remote table section:
查看远程表部分:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm
If this is going to be an ongoing thing, create a db link from instance@serverA to instance@serverB. You can then do anything you have permissions for with data on one instance or the other or both.
如果这是一个正在进行的事情，那么创建一个从instance@serverA到instance@serverB的db链接。然后，您可以对一个实例或另一个实例上的数据或两者上的数据进行任何有权限的操作。
http://psoug.org/definition/CREATE_DATABASE_LINK.htm