Database Links Overview

Database Links: An Overview
—————————
1) What are database links?

Database links are connections between two databases on the
same or different machines.

2) What are database links used for?

To query data on a different database (distributed query)
To do DML on data on a different database (distributed transaction)
To either query or do DML on a non Oracle database (transparent
gateways and generic connectivity with 10g and earlier,database gateways from 11g)

3) The Anatomy of a Database Link

A database link has 4 main parts:

Owner
Link name
Username/password
Host (Service Name)

3.1) The database link OWNER

Like most objects in an Oracle database … database links have an
owner (the user who creates the link) or may be owned by PUBLIC.

If owned by PUBLIC … the database link may be accessed by all
users on the database.

Syntax: CREATE public DATABASE LINK …
CREATE DATABASE LINK …

3.2) The database link NAME

The database link name can be any valid Oracle name.

If global_names = TRUE in the init.ora then the database link name
must be the global name of the remote database
(SELECT GLOBAL_NAME FROM GLOBAL_NAME).

Syntax: CREATE DATABASE LINK kbcook …

3.3) The database link USERNAME

The database link username/password is an OPTIONAL clause. If it
is not specified then the current username/password for the local
database are used to connect to the remote database.

If a username is specified then all connections through that link are
connected as the user specified.

Syntax: CREATE DATABASE LINK kbcook
CONNECT TO scott IDENTIFIED BY tiger …

3.4) The database link HOST

The Host is the entry in the TNSNAMES.ORA for the database that is
being linked to.

Syntax: CREATE DATABASE LINK kbcook
CONNECT TO scott IDENTIFIED BY tiger
USING ‘rtcsol1_v805.us.oracle.com’

4) How do you locate the TNSNAMES.ORA?

If the $TNS_ADMIN environment variable is defined then look in that
directory for the TNSNAMES.ORA.

If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
directory. Alternately the tnsnames may exist in */etc or
/var/opt/oracle as well.

5) How do I locate the HOST in the TNSNAMES.ORA?

The TNSNAMES.ORA will look like:

RTCSOL1_V805.US.ORACLE.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
(CONNECT_DATA = (SID = V805))
)

The host name is everything before the =

Note: You can specify the full description as the host

Syntax: CREATE DATABASE LINK kbcook
CONNECT TO scott IDENTIFIED BY tiger
using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = rtcsol1)(PORT = 1521))
(CONNECT_DATA = (SID = V805))’

6) How a database link works

Step 1: SELECT * FROM TABLE@DBLINK

Step 2: The database will resolve DBLINK to a host name … it will
use the TNSNAMES.ORA unless fully described

Step 3: Naming resolution (DNS, NIS etc) will resolve the HOST to a
TCP/IP address

Step 4: A connection will be made to a listener at the TCP/IP address

Step 5: The listener for the PORT will resolve the SID and finish
the connection to the database (uses the listener.ora to
determine if it is listening for connections to that sid)

7) How do you locate the LISTENER.ORA?

If the $TNS_ADMIN environment variable is defined then look in that
directory for the LISTENER.ORA.

If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
directory. Alternately the listener.ora may exist in */etc or
/var/opt/oracle as well.

8) How do I locate the HOST in the LISTENER.ORA?

The LISTENER.ora will look like:

LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 14000))
)
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=V805)
(ORACLE_HOME=/u04/app/oracle/product/8.0.5)
)
)
)

10) Additional Clauses

The SHARED and AUTHENTICATED BY clauses are used to establish a multi-threaded database connection and both are required to do so.

11) How to check if a Database Link is private / public or shared/not shared:
If the value of the OWNER column of the view DBA_DB_LINKS is PUBLIC,
it is a public database link. All other values indicate it is a private
database link.

SQL> select OWNER from dba_db_links where DB_LINK = ‘MYLINK’;

To check for shared database links, query sys.link$
SQL> select NAME from sys.link$ where AUTHUSR is not null;

Advertisements

One Response

  1. Wonderful, what a web site it is! This weblog
    gives useful data to us, keep it up.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: