Simple service configuration for DB :
<servicename> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = <IP/HOSTNAME>) (Port = <PORT>) ) ) (CONNECT_DATA = (SID = <SIDNAME>) ) )
Service configuration for RAC DB :
RAC10G.GENCSCP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 62.185.197.213)(PORT = 1523)) (ADDRESS = (PROTOCOL = TCP)(HOST = 62.185.197.215)(PORT = 1523)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.GENCSCP) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) )
Check current links :
set linesize 4000 col owner for a10 col username for a10 heading "USER" col db_link for a30 col host for a30 heading "SERVICE" SELECT * FROM DBA_DB_LINKS;
Check opened Link connections :
COL DB_LINK FORMAT A25 COL OWNER_ID FORMAT 99999 HEADING "OWNID" COL LOGGED_ON FORMAT A5 HEADING "LOGON" COL HETEROGENEOUS FORMAT A5 HEADING "HETER" COL PROTOCOL FORMAT A8 COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR" COL IN_TRANSACTION FORMAT A3 HEADING "TXN" COL UPDATE_SENT FORMAT A6 HEADING "UPDATE" COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S" SELECT * FROM V$DBLINK /
Create new Public DB link :
create public database link <LINKNAME> connect to <TARGETUSER> identified by <TARGETPSW> using '<SERVICENAME>';
Create new DB link with different owner :
grant create database link to <USER>; / create procedure <USER>.<PROCNAME> is begin execute immediate ' create database link <LINKNAME> connect to <TARGETUSER> identified by <TARGETPSW> using ''<SERVICENAME>'' '; end; / exec <USER>.<PROCNAME>; / drop procedure <USER>.<PROCNAME>; /
Drop Database link of different user :
create procedure <USER>.<PROCNAME> is begin execute immediate ' drop database link <LINKNAME>'; end; / exec <USER>.<PROCNAME>; drop procedure <USER>.<PROCNAME>;