oracle_client [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Client connection ======

===== Service names in tnsnames.ora =====

**Simple service configuration for DB :**
<sxh bash>
<servicename> =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (Host = <IP/HOSTNAME>)
          (Port = <PORT>)
        )
    )
    (CONNECT_DATA =
       (SID = <SIDNAME>)
    )
)
</sxh>

** Service configuration for RAC DB :**
<sxh bash>
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)
      )
    )
  )
</sxh>

===== Database links =====

**Check current links :**
<sxh sql>
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;
</sxh>

**Check opened Link connections :**
<sxh sql>
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
/
</sxh>

**Create new Public DB link :**
<sxh sql>
create public database link <LINKNAME> connect to <TARGETUSER> identified by <TARGETPSW> using '<SERVICENAME>';
</sxh>

**Create new DB link with different owner :**
<sxh sql>
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>;
/
</sxh>

**Drop Database link of different user :**
<sxh sql>
create procedure <USER>.<PROCNAME>
is
begin
execute immediate '
drop database link <LINKNAME>';
end;
/
 
exec <USER>.<PROCNAME>;

drop procedure <USER>.<PROCNAME>;
</sxh>