Client connection

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