Solved

database-link

Posted on 2011-03-19
11
361 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 69

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
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: 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 69

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 69

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 69

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 69

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

765 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