Scenario:

Create database links owned by application user whose credentials are not shared with DBA

In Reality:

For creating private database link, you will need to connect to user & then create it. If you try to create it using SYS as <owner>.<db_link_name>, Oracle will create DB LINK with name <owner>.<db_link_name> under SYS schema. Example..

SQL> create DATABASE LINK OWN1.DBLINK1 CONNECT TO test_link IDENTIFIED BY test_link USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test1.test1.com) (PORT=1606)) (CONNECT_DATA= (SERVICE_NAME=TEST1)))';
Database link created.
SQL> select OWNER,DB_LINK from dba_db_links;
OWNER    DB_LINK
-------- ---------------
SYS      OWN1.DBLINK1

Solution:

Use the following script to create private database link from SYS into application schema. This will be useful to create the database links sitting under different schemas.

Contents of script:

Script Name: create_schema_dblink.sh

username=$1
db_link=$2
sqlplus /nolog <<!
spool $1_$2_create_dblink.log
conn / as sysdba
prompt " DB Link Before create"
set lines 100
col db_link for a30
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
CREATE or replace PROCEDURE $1.create_db_link AS
strg varchar2(1000);
BEGIN
strg:= 'create DATABASE LINK $2 CONNECT TO test_link IDENTIFIED BY test_link USING '''||'(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test1.test1.com) (PORT=1606)) (CONNECT_DATA= (SERVICE_NAME=TEST1)))''' ;
EXECUTE IMMEDIATE strg;
END ;
/
execute $1.create_db_link;
drop procedure $1.create_db_link;
prompt " DB Link After create"
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
!

Script Execution:

Usage:  sh create_schema_dblink.sh  <Application Schema> <DB Link Name>

[test@test.test.com~]$ sh create_schema_dblink.sh own1 DBLINK1
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 17 16:49:40 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> Connected.
SQL> " DB Link Before create"
SQL> 
no rows selected
SQL> 2 3 4 5 6 7
Procedure created.
SQL>
PL/SQL procedure successfully completed.
SQL>
Procedure dropped.
SQL> " DB Link After create"
SQL>
OWNER   DB_LINK
-----   ------------------------------
OWN1    DBLINK1
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Make sure user under which db link needs to be created has ‘CREATE DATABASE LINK’ privilege, else this script will fail with following error:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “OWN1.CREATE_DB_LINK”, line 5
ORA-06512: at line 1

For dropping private database link owned by application schema using SYS user, use following link:

how-to-drop-other-schemas-database-link-using-sys-user

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath