Re: Using two servers simultaneously

From: <fitzjarrell_at_cox.net>
Date: 3 Apr 2005 23:17:14 -0700
Message-ID: <1112595434.277981.263840@o13g2000cwo.googlegroups.com>

kerul4u wrote:
> hi sameer,
>
> to connect with other database from within u currently logged in
> you have to create database link..
> before creating link u must grant priviliges for create database
link.
> there are two kind of links
> 1 Local
> 2 Public
> here is a example:
> CREATE DATABASE LINK local(*this is a link type)
> CONNECT TO emp(*this is a database name IDENTIFIED BY scott(*this
is
> a user)
> USING 'tiger'(*and finally this is password);
> and u can use link as
> select * from employee_at_local;(*first table name followed by @ and
link
> name);
>
> Before create links don't forget to grant priviliges to a user, by
> which u r logged into your database
>
> ....
> Kerul(DBA)

Let's see how your link performs:

SQL> create database link local
  2 connect to emp identified by scott
  3 using 'tiger';

Database link created.

SQL> select * from employee_at_local;
select * from employee_at_local

                       *

ERROR at line 1:
ORA-12154: TNS:could not resolve service name

Now let's see why it failed:

> here is a example:
> CREATE DATABASE LINK local(*this is a link type)
> CONNECT TO emp(*this is a database name IDENTIFIED BY scott(*this
is
> a user)
> USING 'tiger'(*and finally this is password);

Aside from having all of the parameters completely wrong ...

Let's correct this with some proper instruction. To create a proper database link one must do the following:

create [public] database link <linkname> connect to <username> identified by <password> using <'tnanames.ora service name'>;

You create private database links by omitting the [public] keyword. And there is no 'local' type of link; simply private and public. A private database link is one where only the user account creating it has access. A public database link is, or at least should be, self-explanatory. As an example:

create database link mylink
connect to scott identified by tiger
using 'mydb';

Presuming you have a tnsnames.ora entry named 'mydb', and it connects to a database where 'scott' is a valid username and 'tiger' is the password, you can do this:

select * from emp_at_mylink;

Such a query will return results from the remote database into the current session window as long as a table named EMP exists for Scott.

I hope this helps.

David Fitzjarrell Received on Mon Apr 04 2005 - 01:17:14 CDT