Home » RDBMS Server » Networking and Gateways » How to create a DB link
How to create a DB link [message #227125] Tue, 27 March 2007 06:58 Go to next message
donind
Messages: 95
Registered: February 2007
Member
HI,

Here there are two databases A and B.

The Database A is the one where I am working. I need to get a data from Database B by connecting thru DB link.

Database
A
Table1

DataBase
B
Table2

I had to get data from database (B) Table2 and insert it in database (A) table1.

For that do I need any priviliges to create DB link

Any example will really help me.

Thanks in advance
Re: How to create a DB link [message #227127 is a reply to message #227125] Tue, 27 March 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read this: CREATE DATABASE LINK

Regards
Michel
Re: How to create a DB link [message #227131 is a reply to message #227127] Tue, 27 March 2007 07:16 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
How to know on which service its running???

Re: How to create a DB link [message #227134 is a reply to message #227131] Tue, 27 March 2007 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I assume the "service" you're talking about is the service name you have to provide in the "using" clause, so scan the tnsnames.ora file for your targer instance.

Regards
Michel
Re: How to create a DB link [message #227135 is a reply to message #227131] Tue, 27 March 2007 07:24 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

How to know on which service its running???

Which Service ?

you can check >c:\>net start< for database service
regards
Taj
Re: How to create a DB link [message #227172 is a reply to message #227135] Tue, 27 March 2007 08:17 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
How to know whether my dblink is active or not

This is my TNS entry

hello =
(DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=hello.com)(PORT=1550))
(CONNECT_DATA = (SID = orcl))
)

create database link link1
connect to scott identified by tiger
using 'orcl';

when i am executing the below querry its errored out.

Error:TNS could not resolve the connect identifier specified.


I am connecting to client server thats different server. Do i need to have some privileges and any parametrs to set.

Thanks

Re: How to create a DB link [message #227186 is a reply to message #227172] Tue, 27 March 2007 08:33 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,


Error:TNS could not resolve the connect identifier specified.

You should check your tnsnames.ora file for service_name and check service is respond through NETCA.

SQL> conn scott/tiger@hgc
Connected.
SQL> create table hgc  as select * from emp;

Table created.

SQL> conn scott/tiger@db01
Connected.
SQL> create database link link1
  2  connect to scott identified by tiger
  3  using 'HGC';

Database link created.

SQL> create table test as select * from hgc@link1;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
        10

SQL>




After Michal Post i will clear i have two database
1. hgc
2. db01

1. database hgc.

TNS_ENTRY is >>>hgc<<< and Oracle_sid is >>>oramfe<<<
2. database db01
TNS_ENTRY is >>>db01<<< and oracle_sid is >>>db01<<<

My TNSNAMES.ORA file.
DB01 =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db01)
    )
  )

HGC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oramfe)
    )
  )

[Updated on: Tue, 27 March 2007 09:30]

Report message to a moderator

Re: How to create a DB link [message #227205 is a reply to message #227172] Tue, 27 March 2007 09:15 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

service name is (in this case) tns entry not Oracle SID.

Regards
Michel
Previous Topic: How to Connect to Oracle
Next Topic: ORA-12154 only when logging in with my own login.
Goto Forum:
  


Current Time: Fri Dec 09 19:17:05 CST 2016

Total time taken to generate the page: 0.09693 seconds