7

I have created a db link AM_ADMIN through SQL Developer:

enter image description here

I am trying to check if it is a valid database link. When I test it from the SQL Developer wizard it is successful. But when I use the below command:

SELECT * FROM dual@AM_ADMIN

I'm getting the error:

ORA-02019: connection description for remote database not found

How can I rectify this error?

SaschaM78
  • 4,376
  • 4
  • 33
  • 42
Deepansh Anand
  • 71
  • 1
  • 1
  • 5
  • Have you tried tnsping to the entry referenced on your db link? – vercelli Sep 01 '16 at 11:41
  • i tried tnsping on host name and service name both. for service name it was 1 ms for host name i got the error ORA-12504, TNS:listener was not given the SID in CONNECT_DATA ping:-1ms But i guess it is as expected isn't it? – Deepansh Anand Sep 01 '16 at 11:51
  • How did you create the database link, and how are you testing it - what exactly 'shows successfully'? Are you creating it and running this query as different users? Does your `sqlnet.ora` have `names.default_domain` set? (Or are you maybe confusing DB links and TNS aliases?) – Alex Poole Sep 01 '16 at 11:56
  • @Alex i have created the db link via sql developer console (attached in question). when i am testing from console itself by clicking on test db link , it shows db link has been successfully tested. i am running it from same user with the console itself – Deepansh Anand Sep 01 '16 at 12:14
  • @sstan i am using sqldeveloper to run tnsping and select statement. – Deepansh Anand Sep 01 '16 at 12:19
  • 2
    `tnsping` isn't relevant here; your DB link isn't using a TNS alias. The error means it isn't recognising the DB link name. One reason for that is because it may need to be qualified, e.g. `dual@am_admin.world`. – Alex Poole Sep 01 '16 at 12:27
  • The link you created isn't public. 'select * from user_db_links' and verify you see the link listed. If not, you probably created under a different user's account? – Matthew McPeak Sep 01 '16 at 19:28

3 Answers3

3

According to your screenshot you created the database link as AM_ADMIN.INT.xxx. You are calling it without the domain, just as @AM_ADMIN. Unless you have a sqlnet.ora which defines names.default_domain with the same domain value (and have SQL Developer configured to recognise that), you will need to fully-quality the link name when you use it:

SELECT * FROM dual@AM_ADMIN.INT.xxx
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Hi ALex i am sorry i didnt put the whole part there. but still i am getting the same error. – Deepansh Anand Sep 01 '16 at 13:02
  • @DeepanshAnand - it gets ORA-02019 with the fully name from the window - if you copy and paste that from the 'edit database link' window? (I think it was obvious `.xxx` was a dummy value and you should use your full real one). Are you running the query as the same user you created the link under - does `select * from all_db_links` show that full name too? – Alex Poole Sep 01 '16 at 13:30
  • yes i am running the query as the same user. and select * from all_db_links shows me the full name. – Deepansh Anand Sep 01 '16 at 13:40
  • When you created the link did you specify the full name or just `AM_ADMIN`? What does `select global_name from global_name` show? Using the full link name should work in any case. I'm afraid I don't understand what you're seeing, unless you just have a typo in the name somewhere. – Alex Poole Sep 01 '16 at 14:02
  • While creating the link i specified full name . the query Select global ___ shows me Global name as ORD853A.INT.XXX i am not sure what this refers to. should i include this also in the db link. – Deepansh Anand Sep 01 '16 at 17:20
  • @DeepanshAnand - no, the `.INT.XXX` would have been included in the link name if you hadn't provided it. Assuming the XX part does match what you entered, and are using in your query. Since you're hiding the full name (which is understandable) we can't check if there's a typo, but that's the only thing I can immediately think of. – Alex Poole Sep 01 '16 at 17:35
3

This error will occur when a database link is not made PUBLIC, and the user who created the link is different to the user attempting to use the link. For example, creating the link as the SYS user and then trying to use the link as the AM_ADMIN user.

In this instance either make the link PUBLIC (which all users can then access), or grant the AM_ADMIN user the privileges to create a database link.

Steven
  • 1,564
  • 1
  • 22
  • 34
0

I had the same problem and I found out that it was a stupid error caused by (Description = (... HOST = !...). When you create Database Link in USING clause, you should:

  • use the keyword HOST if you are using the IP address
  • use keyword HOSTNAME if you are using the name

I just wanted to share it because I lost half a day trying to figure that out and I couldn't find any information about it...

machnic
  • 2,304
  • 2
  • 17
  • 21
Ali Mika
  • 11