• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

oracle database link

i'm trying to move all tables in x schema of mallela database to y schema of ajay database
using db links. So, i created a database link in y schema using the statement below:

create database link mallela connect to x identified by <password> using 'mallela';

the link has been created, but when i try the statement below:

select * from table@mallela

its throwing me an error saying "ORA-12154: TNS:could not resolve the connect identifier specified"

My tnsnames files is as follows:

mallela =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mallela)
    )
  )

Any ideas?
0
mallelaajay
Asked:
mallelaajay
  • 2
1 Solution
 
sdstuberCommented:
does your database server 's tnsnames.ora have that entry  or your client?


the database server trying to reach mallea must have the entry
0
 
mallelaajayAuthor Commented:
I'm not sure. These are the development and testing servers. I'm trying to move the tables from development environment to test environment. I'm connecting to these databases using oracle client and my tnsnames file looks as follows

mallela =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mallela)
    )
  )

ajay =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ajay)
    )
  )

What else do i need to add to my tnsnames.ora file to use db links?
0
 
sdstuberCommented:
"your"  tnsnames.ora is irrelevant for db links.


that's because your pc, your client don't do anything.  db links are server-to-server.   It doesn't matter if you can connect to both databases.  The db link is so that one database connects to the other.  The db link does not route the data through your connections.  It makes its own.

Thus the only important thing is the database server trying to reach mallea must have the entry.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now