Skip to Main Content
  • Questions
  • Remote link "insert into select from" Gets Errors

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: June 24, 2020 - 6:44 pm UTC

Answered by: Connor McDonald - Last updated: June 30, 2020 - 12:58 am UTC

Category: SQL - Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Viewed 100+ times

You Asked

I am trying to insert data from an Oracle 12c DB into an Oracle 11g DB by remote link. The remote link works fine for selects and normal DML operations, single inserts also work fine. When I use: insert into TableA@RemoteLink (col1,col2,col3) select col1,col2,col3 from TableBLocal; I get error: ORA-02069: global_names parameter must be set to TRUE for this operation. If I set global_names to TRUE using: alter session set global_names = true; then I get error: ORA-02019: connection description for remote database not found. I've named the remote link as the same name as the remote db. Anything else I can try?

and we said...

Once you set global_names to true, your database *domain* also comes into the picture.

Thus if you domain was (say) "acme.com" and your database link is "remotedb", then the tns entry we will be looking for in your tnsnames.ora is "remotedb.acme.com" not just "remotedb".

So you probably just need to add/update your tnsnames.ora file on the database server and you should be good to go.

and you rated our response

  (2 ratings)

Reviews

Still not working

June 26, 2020 - 4:17 pm UTC

Reviewer: Sam Weber from Salk Lake City, UT

Where do I add a followup like this in the ticket?

Not sure why I would edit the TNSNAMES.ora when the DB LINK itself has the connection information in it. But I went ahead and changed the TNSNAMES.ORA on the server I am connected to. I still get the same error when global_names is set to True. Anything else I can try?
Connor McDonald

Followup  

June 29, 2020 - 5:54 am UTC

Where do I add a followup like this in the ticket?


You just did :-)

Not sure why I would edit the TNSNAMES.ora when the DB LINK itself has the connection information in it.


Hard to comment on that, given that you didn't provide any detail on it to us.

But I went ahead and changed the TNSNAMES.ORA on the server

From what to what?
What is the domain?
What is the tns entries in the file?

....

Followup with detail

June 29, 2020 - 9:27 pm UTC

Reviewer: Sam Weber from Salt Lake City, UT

sorry, should have provided more detail. here is the link sql I used:

CREATE PUBLIC DATABASE LINK "LIMS11.SMRTL.LOCAL"
CONNECT TO XXXXXX
IDENTIFIED BY <password>
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxxx)
))';

the domain on the SOURCE db is: smrtl.local
I should mention that the TARGET db does not have a value in the Oracle domain parameter.

Then TNSNAMES entry I edited was on the Source DB in it's Oracle Linux environment:

LIMS11.SMRTL.LOCAL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =xxxxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lims11.smrtl.local)
)
)



Connor McDonald

Followup  

June 30, 2020 - 12:58 am UTC

The target database not having a domain name is probably going to cause you issues, because you are not going to have an alignment between your database link name and the remote name.

Some examples of that here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=names-for-database-links


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database