Networking & DB links
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
---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.
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
ORDER BY owner, db_link;
To get the creation script of the available db links on the database
'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 || ''''
FROM sys.link$ L,
WHERE L.OWNER# = U.USER# ;