Solved

database-link

Posted on 2011-03-19
11
366 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 70

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
 

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 70

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 70

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 70

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 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