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
Categories: Administration, Scripts
Very good information. Lucky me I came across your blog by accident (stumbleupon). I’ve bookmarked it for later!
This piece of writing is genuinely a pleasant one it helps new net people, who are wishing for blogging.
It’s remarkable for me to have a website, which is helpful designed for my know-how.
thanks admin
very nice publish, i certainly love this website, carry on it
Just desire to say your article is as amazing. The clearness
in your post is just nice and i can assume you’re an expert on this subject.
Fine with your permission let me to grab your feed to keep updated with forthcoming post.
Thanks a million and please keep up the gratifying work.
I am now not sure where you’re getting your info, but great topic.
I needs to spend a while finding out much more or working out more.
Thanks for fantastic information I was searching for this information for my
mission.
Good day! This is my first visit to your blog! We are a collection of volunteers and starting a new project in a community in the same niche. Your blog provided us valuable information to work on. You have done a wonderful job!