无法在单个查询中使用两个数据库链接 [英] Unable to use two database links in a single query

问题描述

我试图在一个查询中使用两个数据库链接，一个用于获取数据，另一个用于插入数据.但是我无法在单个查询中使用它们，即使当它们在单独的查询中使用时它们都可以完美地工作.我不能在单个查询中使用单独的数据库链接吗?我要运行的查询就像

I'm trying to use two database links in a single query, one for fetching data and the other to insert data. But I'm unable to use them in a single query, even though both of them work perfectly when they are used in separate queries. Can't I use separate database links in a single query and if so why not? The query I'm trying to run is like

INSERT INTO TABLE1@DBLINK1 SELECT * FROM TABLE2@DBLINK2;

推荐答案

这看起来像是10g的错误. SQL可能通过DBLINK连接多个站点时可能会发生错误6320621，ORA-2019的情况;或5713736，使用DB-LINK ORA-02019插入带有选择选项的语句失败".您可能需要向Oracle提出SR，以便针对您的特定情况进行调查.

This looks like a bug in 10g. Possibly bug 6320621, ORA-2019 OCCURS WHEN SQL CONNECTS MULTIPLE SITES VIA DBLINK'; or 5713736, 'INSERT INTO STATEMENT WITH SELECT OPTION FAILS USING DB-LINK ORA-02019'. You probably need to raise an SR with Oracle to get this investigated for your specific circumstances.

作为一种替代方法，您可以考虑从一个数据库中卸载数据，然后再将其加载到另一个数据库中.例如，可以将expdp和impdp与tables和content=data_only选项一起使用，以两个阶段进行传输.当然，您需要空间来将转储文件存储在中间数据库服务器上，这不太方便.您可以通过源数据库服务器或目标数据库服务器来做到这一点，避免使用中间服务器，但是尚不清楚是否可以直接访问.

As an alternative approach, you could maybe consider unloading the data from one database and then loading it into the other. For example, you could use expdp and impdp, with the tables and content=data_only options, to do the transfer in two stages. Of course you need space to store the dump file on your intermediate database server, and it's rather less convenient. You might be able to do that form the source or destination database servers, avoiding the intermediate, but it's not clear if you have any direct access.

这篇关于无法在单个查询中使用两个数据库链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！