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# ;