{"id":210,"date":"2013-06-25T02:44:33","date_gmt":"2013-06-25T06:44:33","guid":{"rendered":"http:\/\/dbtricks.com\/?p=210"},"modified":"2013-06-25T02:44:33","modified_gmt":"2013-06-25T06:44:33","slug":"create-a-database-link-without-changing-the-tnsnames-ora","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=210","title":{"rendered":"create a database link without changing the TNSNAMES.ORA"},"content":{"rendered":"<p>The basic syntax of creating a public database link is:<br \/>\n<code><br \/>\n<strong>CREATE   PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK<br \/>\n  CONNECT TO schema_name<br \/>\n  IDENTIFIED BY \"schema_password\"<br \/>\n  USING 'tnsnames entry'; <\/strong>  <\/code><\/p>\n<p>However, many times even if you have the database permission to create a db link (CREATE DATABASE LINK system privilege), you have no access to the TNSNAMES.ORA file on the server.<\/p>\n<p>Without adding the entry to the TNSNAMES.ORA file any attempt to select from this database link will result in a <strong>ORA-12154: TNS:could not resolve the connect identifier specified error.<\/strong><br \/>\nThe simple solution is to create a database link without changing the TNSNAMES.ORA file. The syntax is:<\/p>\n<p><code><strong>CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK<\/p>\n<p>  CONNECT TO schema_name IDENTIFIED BY \" schema_password\"  <\/p>\n<p>  USING  <\/p>\n<p>  '(DESCRIPTION=  <\/p>\n<p>    (ADDRESS=  <\/p>\n<p>    (PROTOCOL=TCP)  <\/p>\n<p>    (HOST=hostname-or-IP)  <\/p>\n<p>     (PORT=1521))  <\/p>\n<p>    (CONNECT_DATA=  <\/p>\n<p>     (SID=sid-name)))' <\/p>\n<p>  ;<\/strong><\/code><\/p>\n<p>Since Oracle 10, it is possible to use the abbreviated version <\/p>\n<p><code><strong>CREATE DATABASE LINK NAME.OF.DATABASE.LINK<br \/>\nCONNECT TO schema_name IDENTIFIED BY schema_password<br \/>\nUSING '\/\/hostname-or-IP:1521\/sid';<\/strong> <\/code><\/p>\n<p>It is important to note that while this trick might be great as temporary workaround, it is recommended to use the old fashion TNSNAMES method to create a constant database link. This is especially true in a multiple servers environment where a databases can be copied from one environment to another (prod to test for example). In such cases, the copied database might include links to prod machines while the tnsnames.ora file will still point to the correct machine.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The basic syntax of creating a public database link is: CREATE PUBLIC DATABASE LINK NAME.OF.DATABASE.LINK CONNECT TO schema_name IDENTIFIED BY &#8220;schema_password&#8221; USING &#8216;tnsnames entry&#8217;; However, many times even if you have the database permission to create a db link (CREATE DATABASE LINK system privilege), you have no access to the TNSNAMES.ORA file on the server. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-210","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=210"}],"version-history":[{"count":3,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/210\/revisions"}],"predecessor-version":[{"id":213,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/210\/revisions\/213"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}