Solved

database-link

Posted on 2011-03-19
11
373 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

623 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