need help creating db links
Hi all,
I have 2 dbs ora1 and ora2 on the same linux box (host = linbox) running 8.1.6.
ora1 service name = ora1
ora1 user = scott
ora2 service name = ora2
ora2 user = mary
If I want to create a public db link from mary's account (create public db link privilege already granted to mary) to scott's schema to acces the emp table. I tried the following:
SQL> create public database link ora1.linbox connect to scott identified by tiger using 'ora1';
Is this correct?
When I try to access the emp table I get the following error:
SQL> select count(*) from scott.emp@ora1.linbox;
ORA-12154: TNS: could not resolve service name
My tnsnames.ora file has the following entries:
ORA1.LINBOX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora1)
)
)
Am I doing something majorly wrong? Also how would scott grant select on emp to mary (user in anohter db)? Thanks
Re: need help creating db links
Quote:
Originally posted by padawin
Hi all,
I have 2 dbs ora1 and ora2 on the same linux box (host = linbox) running 8.1.6.
ora1 service name = ora1
ora1 user = scott
ora2 service name = ora2
ora2 user = mary
If I want to create a public db link from mary's account (create public db link privilege already granted to mary) to scott's schema to acces the emp table. I tried the following:
SQL> create public database link ora1.linbox connect to scott identified by tiger using 'ora1';
Is this correct?
When I try to access the emp table I get the following error:
SQL> select count(*) from scott.emp@ora1.linbox;
ORA-12154: TNS: could not resolve service name
My tnsnames.ora file has the following entries:
ORA1.LINBOX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora1)
)
)
Am I doing something majorly wrong? Also how would scott grant select on emp to mary (user in anohter db)? Thanks
I have got exactly the same error message when I tried some time back.
I have replaced the connection string with the full address and it worked so you replace 'ora1' with the exact address in tnsnames.ora file.