Oracle Database links or DB Links – complete reference for the new oracle DBA career

A new Oracle DBA can find here important information about database links and how to troubleshoot problems with database links.

Oracle performs these tasks before connecting to the remote database:

If the database link name specified in the statement is partial, then Oracle expands the name to contain the domain of the local database as found in the global database name stored in the data dictionary. (You can see the current global database name in the GLOBAL_NAME data dictionary view.)
Oracle first searches for a private database link in your own schema with the same name as the database link in the statement. Then, if necessary, it searches for a public database link with the same name.

Oracle always determines the username and password from the first matching database link (either private or public). If the first matching database link has an associated username and password, then Oracle uses it. If it does not have an associated username and password, then Oracle uses your current username and password.
If the first matching database link has an associated database string, then Oracle uses it. Otherwise Oracle searches for the next matching (public) database link. If no matching database link is found, or if no matching link has an associated database string, then Oracle returns an error.

Oracle uses the database string to access the remote database. After accessing the remote database, if the value of the GLOBAL_NAMES parameter is true, then Oracle verifies that the database.domain portion of the database link name matches the complete global name of the remote database. If this condition is true, then Oracle proceeds with the connection, using the username and password chosen in Step 2. If not, Oracle returns an error.
If the connection using the database string, username, and password is successful, then Oracle attempts to access the specified object on the remote database using the rules for resolving object references and referring to objects in other schemas discussed earlier in this section.

Column	Which Views?	Description
OWNER	All except USER_*	The user who created the database link. If the link is public, then the user is listed as PUBLIC.
DB_LINK	All	The name of the database link.
USERNAME	All	If the link definition includes a fixed user, then this column displays the username of the fixed user. If there is no fixed user, the column is NULL.
PASSWORD	Only USER_*	Not used. Maintained for backward compatibility only.
HOST	All	The net service name used to connect to the remote database.
CREATED	All	Creation time of the database link.

View	Purpose
DBA_DB_LINKS	Lists all database links in the database.
ALL_DB_LINKS	Lists all database links accessible to the connected user.
USER_DB_LINKS	Lists all database links owned by the connected user.

Hi all,

I have two dbs namely commondb and db1.

I have a table named table1 in commondb under scott user.

I have created a dblink link1 from db1 to commondb using scott user id and password.

Also have created a synonym in db1 under synonym.

scott@db1> create synonym table1 for table1@link1;

Synonym created.

scott@db1> Select count(*) from table1;

4800

I created a proxy id scott_prx in db1 and have created synonym for the synonym scott.table1.

sys@db1> create user scott_prx identified by XXXX;

sys@db1> create synonym scott_prx.table1 for scott.table;

synonym created.

Im getting this error while selecting the synonym created under scott_prx.

scott_prx@db1 > select count(*) from table1;

"ORA-02019 Connection description for remote database not found"

How to resolve this?

Is there any alternate way of doing this without creating a db link under proxy id.

Thanks,
Prem

sybrandb

Posts: 5,205
Registered: 08/04/98

Re: ORA-02019 Connection description for remote database not found
Posted: 26-Nov-2008 00:59 in response to: odba8489

Prem,

Did you look up the error ora-2019 in the online documentation at http://tahiti.oracle.com?
Usually this error means the service name you are using in the database link (using ‘<service>’)
can not be found in the tnsnames.ora on the server.

If you didn’t look up the error, can you please be so kind always to look it up first before posting here?
This is a forum of volunteers, and doing some effort yourself, making you more independent and self-reliant, is appreciated.

Hth
—
Sybrand Bakker
Senior Oracle DBA

odba8489

Posts: 121
Registered: 08/24/06

Re: ORA-02019 Connection description for remote database not found
Posted: 26-Nov-2008 01:43 in response to: sybrandb

Thanks for your response.

If it is having problem with the tns entry how come the below statement is working which is refrering the table in commondb

scott@db1> Select count(*) from table1;

4800

Thanks,
Prem

sybrandb

Posts: 5,205
Registered: 08/04/98

Re: ORA-02019 Connection description for remote database not found
Posted: 26-Nov-2008 02:04 in response to: odba8489

Prem,

I looked up the error on your behalf.
I used the 9i manual, as the 10g error manual yet again appears to be incomplete.

ORA-02019 connection description for remote database not found

Cause: An attempt was made to connect or log in to a remote database using a connection description that could not be found.

Action: Specify an existing database link. Query the data dictionary to see all existing database links. See your operating system-specific Net8 documentation for valid connection descriptors.
As yoiu didn’t post the exact statements, I can only assume you created a private database link (the default) instead of a public database link.
So the answer to your question is obvious: You need to create a public database link.
IMO, you could have found out this yourself.

—
Sybrand Bakker
Senior Oracle DBA

Anand…

Posts: 1,962
Registered: 08/29/08

Re: ORA-02019 Connection description for remote database not found
Posted: 26-Nov-2008 02:10 in response to: odba8489

HI..

Is the dblink link1 PUBLIC ?? I think you have created a private dblink from db1 to commandb, that is why the user scott_prx can’t use it.

@Sybrand: Sorry sir, I did not see your reply

Anand

Edited by: Anand… on Nov 26, 2008 3:41 PM

odba8489

Posts: 121
Registered: 08/24/06

Re: ORA-02019 Connection description for remote database not found
Posted: 26-Nov-2008 04:06 in response to: Anand…

It is a private database link

Anand…

Posts: 1,962
Registered: 08/29/08

Re: ORA-02019 Connection description for remote database not found
Posted: 26-Nov-2008 04:12 in response to: odba8489

As its a private dblink scott_prx won’t be able to use it.Create a public database link , then create a synonym table1 for table1@link1 in the scott_prx schema and then try select * from table1 , it should work.

A public database link can be used by any schema in the database

LInk: http://www.ss64.com/ora/link_c.html

HTH
Anand

Edited by: Anand… on Nov 26, 2008 5:42 PM

Determining Which Link Connections Are Open

You may find it useful to determine which database link connections are currently

open in your session. Note that if you connect as SYSDBA, you cannot query a view to

determine all the links open for all sessions; you can only access the link information

in the session within which you are working.

The following views show the database link connections that are currently open in

your current session:

Column Which Views? Description

OWNER All except USER_* The user who created the database link. If the link is

public, then the user is listed as PUBLIC.

DB_LINK All The name of the database link.

USERNAME All If the link definition includes a fixed user, then this

column displays the username of the fixed user. If

there is no fixed user, the column is NULL.

PASSWORD Only USER_* Not used. Maintained for backward compatibility

only.

HOST All The net service name used to connect to the remote

database.

CREATED All Creation time of the database link.

View Purpose

V$DBLINK Lists all open database links in your session, that is, all database

links with the IN_TRANSACTION column set to YES.

GV$DBLINK Lists all open database links in your session along with their

corresponding instances. This view is useful in an Oracle Real

Application Clusters configuration.

Database links are either private or public. If they are private, then only the user who

created the link has access; if they are public, then all database users have access.

Drop the database link by using

Oracle Database links or DB Links – complete reference for the new oracle DBA career

Related

Categories