Database Links

Check

@/u01/dba/dbs/dblinks.sql 

SET LINESIZE 132COLUMN host FORMAT A10COLUMN db_link FORMAT A25COLUMN owner FORMAT A20COLUMN username FORMAT A20COLUMN created FORMAT A20SELECT * FROM dba_db_links/ 

Create

Prerequisites

Ensure network name is resolvable...

ping target

where target is the hostname (from DNS or /etc/hosts) of the target server.

Ensure stanzas for target databases are in tnsnames.ora. Check the tnsnames.ora config is resolvable...

tnsping TNS_ALIAS

Create Database Link

sqlplus / as sysdba

CREATE PUBLIC DATABASE LINK &LINK USING '&TNS_ALIAS';

CONNECT AS &USER1

CREATE DATABASE LINK "&LINK" CONNECT TO &TARGETUSER1 IDENTIFIED BY "&TARGETPWD1";

CONNECT AS &USER2

CREATE DATABASE LINK "&LINK" CONNECT TO &TARGETUSER2 IDENTIFIED BY "&TARGETPWD2";

Change Database Link Password

Since 11gR2, it is possible to change the password in a database link without dropping and recreating it...

ALTER DATABASE LINK &dblinkname CONNECT TO &username IDENTIFIED BY &newpassword; 

GLOBAL_NAMES

This is a CIS Benchmark requirement

Check

SELECT UPPER(VALUE)

  FROM V$SYSTEM_PARAMETER

 WHERE UPPER(NAME)='GLOBAL_NAMES'; 

Multi-tenant version...

SELECT DISTINCT UPPER(V.VALUE),

       DECODE (V.CON_ID,0,(SELECT NAME FROM V$DATABASE),

                        1,(SELECT NAME FROM V$DATABASE),

                          (SELECT NAME FROM V$PDBS B WHERE V.CON_ID = B.CON_ID))

  FROM V$SYSTEM_PARAMETER V

 WHERE UPPER(NAME) = 'GLOBAL_NAMES'; 

Change

ALTER SYSTEM SET GLOBAL_NAMES = TRUE SCOPE = SPFILE;

Bibliography