Database Technologies‎ > ‎Oracle‎ > ‎

Networking & DB links

Network configuration

Listener is utility which listens for incoming request at particular port and then
hands of the connection to database.

Main network configuration files are located in
%oracle_home%/NETWORK/Admin/  directory and are the following

listener.ora         -For configuration of Listeners, and the SID's to listen for
sqlnet.ora           -Miscellaneous Naming methods, how to perform authenticaton
tnsnames.ora    -local naming methods, creat alias for datbase,
 
--Below SID_LIST_LISTENETR is the list of SID's the listerner LISTERNER is
--listening to

SID_LIST_LISTENER=
  (          SID_LIST=
                     (SID_DESC=
                       (SID_NAME=orcl)
                       (ORACLE_HOME=/oracle9i)
                      )
                      (SID_DESC=
                      (SID_NAME=sales)
                      (ORACLE_HOME=/oracle9i)
                      )
 )
 
---This is the listener LISTENER configured which is listening to above sids
---You could rename. You could configure multiple listener on same server
---but with different ports ofcourse.

 LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=myserver)(PORT=1521))
    )
  )
 


Distributed Queries,

A database link is a schema object in one database that enables you to access objects on another database

We have two types of db links public and private.

Public  :  Link is accesible to all users.
Private : Other users cant access

create public database link OTHERDB
connect to scott identified by tiger using '<tns of OTHERDB>'

if you dont use public it is private and is owned by the current user.

To check if the other db in working

select sysdate from dual@OTHERDB;

should return the date as in other database.

Now the db links has been created you could use the db link to access/modify the data
across the other database using distributed Queries
 
To get list of all the db links on the database
 
SET LINES 120
COLUMN db_link FORMAT A20
COLUMN host FORMAT A25
SELECT owner,
       db_link,
       username,
       host
FROM   dba_db_links
ORDER BY owner, db_link;
 
 
To get the creation script of the available db links on the database
 
SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)   
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''  
||chr(10)||';' TEXT
FROM  sys.link$       L,     
sys.user$       U
WHERE L.OWNER# = U.USER# ;

Comments