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