(window.webpackJsonp=window.webpackJsonp||[]).push([[2339],{2747:function(e,a,s){"use strict";s.r(a);var t=s(31),r=Object(t.a)({},(function(){var e=this,a=e.$createElement,s=e._self._c||a;return s("ContentSlotsDistributor",{attrs:{"slot-key":e.$parent.slotKey}},[s("h1",{attrs:{id:"database-links"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#database-links"}},[e._v("#")]),e._v(" Database Links")]),e._v(" "),s("h2",{attrs:{id:"creating-a-database-link"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#creating-a-database-link"}},[e._v("#")]),e._v(" Creating a database link")]),e._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("CREATE")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("DATABASE")]),e._v(" LINK dblink_name \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("CONNECT")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("TO")]),e._v(" remote_username \nIDENTIFIED "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("BY")]),e._v(" remote_password \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("USING")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[e._v("'tns_service_name'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(";")]),e._v("\n\n")])])]),s("p",[e._v("The remote DB will then be accessible in the following way:")]),e._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("SELECT")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v("*")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("FROM")]),e._v(" MY_TABLE"),s("span",{pre:!0,attrs:{class:"token variable"}},[e._v("@dblink_name")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(";")]),e._v("\n\n")])])]),s("p",[e._v("To test a database link connection without needing to know any of the object names in the linked database, use the following query:")]),e._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("SELECT")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v("*")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("FROM")]),e._v(" DUAL"),s("span",{pre:!0,attrs:{class:"token variable"}},[e._v("@dblink_name")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(";")]),e._v("\n\n")])])]),s("p",[e._v("To explicitly specify a domain for the linked database service, the domain name is added to the "),s("code",[e._v("USING")]),e._v(" statement. For example:")]),e._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("USING")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[e._v("'tns_service_name.WORLD'")]),e._v("\n\n")])])]),s("p",[e._v("If no domain name is explicitly specified, Oracle uses the domain of the database in which the link is being created.")]),e._v(" "),s("p",[e._v("Oracle documentation for database link creation:")]),e._v(" "),s("ul",[s("li",[e._v("10g: "),s("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm",target:"_blank",rel:"noopener noreferrer"}},[e._v("https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm"),s("OutboundLink")],1)]),e._v(" "),s("li",[e._v("11g: "),s("a",{attrs:{href:"https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm",target:"_blank",rel:"noopener noreferrer"}},[e._v("https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm"),s("OutboundLink")],1)]),e._v(" "),s("li",[e._v("12g: "),s("a",{attrs:{href:"https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205",target:"_blank",rel:"noopener noreferrer"}},[e._v("https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205"),s("OutboundLink")],1)])]),e._v(" "),s("h2",{attrs:{id:"create-database-link"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#create-database-link"}},[e._v("#")]),e._v(" Create Database Link")]),e._v(" "),s("p",[e._v('Let we assume we have two databases "ORA1" and "ORA2". We can access the objects of "ORA2" from database "ORA1" using a database link.')]),e._v(" "),s("p",[e._v("Prerequisites:\nFor creating a private Database link you need a "),s("code",[e._v("CREATE DATABASE LINK")]),e._v(" privilege.\nFor creating a private Database link you need a "),s("code",[e._v("CREATE PUBLIC DATABASE LINK")]),e._v(" privilege.")]),e._v(" "),s("p",[e._v("*"),s("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/gateways.102/b16223/net.htm#i1153556",target:"_blank",rel:"noopener noreferrer"}},[e._v("Oracle Net"),s("OutboundLink")],1),e._v(" must be present on both the instances.")]),e._v(" "),s("p",[e._v("How to create a database link:")]),e._v(" "),s("p",[e._v("From ORA1:")]),e._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("SQL")]),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v(">")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("create")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v("<")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("public")]),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v(">")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("database")]),e._v(" link ora2 "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("connect")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("to")]),e._v(" user1 identified "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("by")]),e._v(" pass1 "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("using")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v("<")]),e._v("tns name "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("of")]),e._v(" ora2"),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v(">")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(";")]),e._v("\n\n")])])]),s("p",[e._v("Database link created.")]),e._v(" "),s("p",[e._v("Now that we have the DB link set up, we can prove that by running the following from ORA1:")]),e._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("SQL")]),s("span",{pre:!0,attrs:{class:"token operator"}},[e._v(">")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("Select")]),e._v(" name "),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("from")]),e._v(" V$"),s("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("DATABASE")]),s("span",{pre:!0,attrs:{class:"token variable"}},[e._v("@ORA2")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(";")]),e._v(" "),s("span",{pre:!0,attrs:{class:"token comment"}},[e._v("-- should return ORA2")]),e._v("\n\n")])])]),s("p",[e._v('You can also access the DB Objects of "ORA2" from "ORA1", given the user '),s("code",[e._v("user1")]),e._v(" has the "),s("code",[e._v("SELECT")]),e._v(" privilege on those objects on ORA2 (such as TABLE1 below):")]),e._v(" "),s("div",{staticClass:"language- extra-class"},[s("pre",{pre:!0,attrs:{class:"language-text"}},[s("code",[e._v("\n SELECT COUNT(*) FROM TABLE1@ORA2;\n\n")])])]),s("p",[e._v("Pre-requistes:")]),e._v(" "),s("ul",[s("li",[e._v("Both databases must be up and running (opened).")]),e._v(" "),s("li",[e._v("Both database listeners must be up and running.")]),e._v(" "),s("li",[e._v("TNS must be configured correctly.")]),e._v(" "),s("li",[e._v("User user1 must be present in ORA2 database, password must be checked and verified.")]),e._v(" "),s("li",[e._v("User user1 must have at least the "),s("code",[e._v("SELECT")]),e._v(" privilege, or any other required to access the objects on ORA2.")])])])}),[],!1,null,null,null);a.default=r.exports}}]);