Solved

database-link

Posted on 2011-03-19
11
352 Views
Last Modified: 2012-05-11

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
0
Comment
Question by:nobleit
  • 5
  • 4
  • 2
11 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 35175261
-- 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
 
LVL 18

Expert Comment

by:sventhan
ID: 35175281
CREATE DATABASE LINK remotedb
CONNECT TO SCOT IDENTIFIED BY TIGER
USING 'conn_link'; --- Replace conn_link  FROM YOUR TNSNAMES
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 35175552
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
 

Author Comment

by:nobleit
ID: 35175850
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 35176095
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:nobleit
ID: 35176363

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
 
LVL 68

Expert Comment

by:Qlemo
ID: 35176397
Then sys/newpasspord123 is no valid login/password combination. Try another known user having access to that table in company.
0
 

Author Comment

by:nobleit
ID: 35176572
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 35176594
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
 

Author Comment

by:nobleit
ID: 35176786
what about tnsnames.ora?
Could you give me an example dblink using modifying tnsnames.ora
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 35177055
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now