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