database-link


I have two databases company and  orcl123.

I am trying to create database link between these two dbs.
scott user is in the orcl123 database.
sql>create database link remotedb connect to scott identified by tiger using ' ';
What should I add in the ' ' part in the command
nobleitAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
COMPANY = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = JustAnotherPC)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = company))
  )

or

COMPANY = JustAnotherPC/company

After that, you can leave your dblink as-is -   ...  using 'company';
0
 
sventhanCommented:
-- from tnsnaMES
conn_link =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orabase)
    )
  )


conn uwclass/uwclass

CREATE DATABASE LINK conn_user
USING 'conn_link';

desc user_db_links

set linesize 121
col db_link format a20
col username format a20
col password format a20
col host format a20

SELECT * FROM user_db_links;

SELECT * FROM all_db_links;

SELECT table_name, tablespace_name FROM user_tables@conn_user;

http://psoug.org/reference/db_link.html
0
 
sventhanCommented:
CREATE DATABASE LINK remotedb
CONNECT TO SCOT IDENTIFIED BY TIGER
USING 'conn_link'; --- Replace conn_link  FROM YOUR TNSNAMES
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If you are working on 10g or above, a connection string is much easier constructed by just using
   server/instance_name
instead of all the old-fashioned TNS connection string stuff.
0
 
nobleitAuthor Commented:
Still I have problem..

This is what I did

SQL> create database link db_link1
  2  connect to sys identified by newpassword123
  3  using 'db_service';

Database link created.
Elapsed: 00:00:05.90
SQL> select * from emp@db_link1;
select * from emp@db_link1
                  *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
Elapsed: 00:00:13.62

orcl123 is the database where the database link is created.
emp is the table in the company database..
where should I modify..
Below is my tnsnames.ora

COMPANY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mathew-b8f381b1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = company)
    )
  )

ORCL123 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mathew-b8f381b1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl123)
    )
  )


0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You need to use your TNS name after "using":
     create database link db_link connect to sys identified by newpassword123
     using 'company';

or the EazyConnect string, which does not need TNSNames.Ora:
     create database link db_link connect to sys identified by newpassword123
     using 'mathew-b8f381b1/company';

0
 
nobleitAuthor Commented:

after that i tried to access emp table in company using this
an error is shown like this

SQL> select * from emp@orcl_company;
select * from emp@orcl_company
                  *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ORCL_COMPANY

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Then sys/newpasspord123 is no valid login/password combination. Try another known user having access to that table in company.
0
 
nobleitAuthor Commented:
Dear Qlemo thank you very much for your valuable advice..it is working fine.
Let me ask you one more question ...
What should we do if that database and table is residing in another machine.?
Thanks..
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It's not different. You need to provide server and instance name anyway when you create the dblink, so it does not matter if the target db is located on another machine.
0
 
nobleitAuthor Commented:
what about tnsnames.ora?
Could you give me an example dblink using modifying tnsnames.ora
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.