{"id":233,"date":"2015-10-14T20:27:58","date_gmt":"2015-10-14T20:27:58","guid":{"rendered":"https:\/\/dbadave.co.uk\/?p=233"},"modified":"2016-02-09T22:17:17","modified_gmt":"2016-02-09T22:17:17","slug":"oracle-create-database-link-command","status":"publish","type":"post","link":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/","title":{"rendered":"Oracle Create Database Link Command"},"content":{"rendered":"

If you’ve got two Oracle databases and you want to query the data from one database whilst connected to the other one, you can create a database link between the two. Executing the ‘CREATE DATABASE LINK’ command in the first database will create a schema object in it\u00a0which allows you to access objects\/data in the second one.<\/p>\n

In fact, the second database doesn’t even need to be an Oracle database. If you do want to access a non-Oracle database through a database link though you’ll need to use Oracle Heterogeneous Services<\/a>.<\/p>\n

Once you’ve created a database link, you can then use it to access the objects in the second database. You can use INSERT, UPDATE, DELETE and LOCK TABLE commands to access the data in the remote tables and views. You do this by adding the name of the database link to the table or view name in the SQL statement, along with the @ character, for example:<\/p>\n

SELECT * FROM REMOTE_TABLE@MY_DB_LINK;<\/em><\/p>\n

CREATE DATABASE LINK Syntax<\/h2>\n

The basic syntax for the ‘CREATE DATABASE LINK’ command is as follows:<\/p>\n

CREATE [PUBLIC] DATABASE LINK <link_name> <\/em>
\nCONNECT TO <user_name> <\/em>
\nIDENTIFIED BY <password> <\/em>
\nUSING ‘<service_name>’;<\/em><\/p>\n

The PUBLIC clause makes the database link available to all database users. If you don’t specify it, access to the link is private.<\/p>\n

Example Command<\/h2>\n

Below is an example CREATE DATABASE LINK command:<\/p>\n

CREATE DATABASE LINK my_db_link<\/em>
\nCONNECT TO my_user<\/em>
\nIDENTIFIED BY my_password<\/em>
\nUSING ‘my_service’;<\/em><\/p>\n

This command will create a private database link to the database whose service name is ‘my_service’. The link will be a private link and the connection will be made using the username\/password ‘my_user\/my_password’.<\/p>\n

To query the data in the MY_TABLE table in the remote database, you could then execute a query such as:\u00a0SELECT * FROM MY_TABLE@my_db_link;<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

If you’ve got two Oracle databases and you want to query the data from one database whilst connected to the other one, you can create a database link between the two. Executing the ‘CREATE DATABASE LINK’ command in the first database will create a schema object in it\u00a0which allows you to access objects\/data in the … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":357,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"yoast_head":"\nCreate Database Link - Oracle Command<\/title>\n<meta name=\"description\" content=\"The Oracle CREATE DATABASE LINK command lets you create a link between two databases and then query the tables and views in one from the other. READ MORE.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create Database Link - Oracle Command\" \/>\n<meta property=\"og:description\" content=\"The Oracle CREATE DATABASE LINK command lets you create a link between two databases and then query the tables and views in one from the other. READ MORE.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/\" \/>\n<meta property=\"og:site_name\" content=\"DBA Dave - Oracle Training\" \/>\n<meta property=\"article:published_time\" content=\"2015-10-14T20:27:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-02-09T22:17:17+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"770\" \/>\n\t<meta property=\"og:image:height\" content=\"340\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40\"},\"headline\":\"Oracle Create Database Link Command\",\"datePublished\":\"2015-10-14T20:27:58+00:00\",\"dateModified\":\"2016-02-09T22:17:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/\"},\"wordCount\":332,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbadave.co.uk\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg\",\"articleSection\":[\"Oracle Training\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/\",\"url\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/\",\"name\":\"Create Database Link - Oracle Command\",\"isPartOf\":{\"@id\":\"https:\/\/dbadave.co.uk\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg\",\"datePublished\":\"2015-10-14T20:27:58+00:00\",\"dateModified\":\"2016-02-09T22:17:17+00:00\",\"description\":\"The Oracle CREATE DATABASE LINK command lets you create a link between two databases and then query the tables and views in one from the other. READ MORE.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage\",\"url\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg\",\"contentUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg\",\"width\":770,\"height\":340,\"caption\":\"Oracle Create Database Link\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbadave.co.uk\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Create Database Link Command\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbadave.co.uk\/#website\",\"url\":\"https:\/\/dbadave.co.uk\/\",\"name\":\"DBA Dave - Oracle Training\",\"description\":\"Expert DBA Tips & Advice\",\"publisher\":{\"@id\":\"https:\/\/dbadave.co.uk\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbadave.co.uk\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbadave.co.uk\/#organization\",\"name\":\"DBA Dave\",\"url\":\"https:\/\/dbadave.co.uk\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png\",\"contentUrl\":\"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png\",\"width\":170,\"height\":50,\"caption\":\"DBA Dave\"},\"image\":{\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbadave.co.uk\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"url\":\"https:\/\/dbadave.co.uk\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Create Database Link - Oracle Command","description":"The Oracle CREATE DATABASE LINK command lets you create a link between two databases and then query the tables and views in one from the other. READ MORE.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/","og_locale":"en_US","og_type":"article","og_title":"Create Database Link - Oracle Command","og_description":"The Oracle CREATE DATABASE LINK command lets you create a link between two databases and then query the tables and views in one from the other. READ MORE.","og_url":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/","og_site_name":"DBA Dave - Oracle Training","article_published_time":"2015-10-14T20:27:58+00:00","article_modified_time":"2016-02-09T22:17:17+00:00","og_image":[{"width":770,"height":340,"url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg","type":"image\/jpeg"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#article","isPartOf":{"@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/"},"author":{"name":"admin","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40"},"headline":"Oracle Create Database Link Command","datePublished":"2015-10-14T20:27:58+00:00","dateModified":"2016-02-09T22:17:17+00:00","mainEntityOfPage":{"@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/"},"wordCount":332,"commentCount":0,"publisher":{"@id":"https:\/\/dbadave.co.uk\/#organization"},"image":{"@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage"},"thumbnailUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg","articleSection":["Oracle Training"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/","url":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/","name":"Create Database Link - Oracle Command","isPartOf":{"@id":"https:\/\/dbadave.co.uk\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage"},"image":{"@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage"},"thumbnailUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg","datePublished":"2015-10-14T20:27:58+00:00","dateModified":"2016-02-09T22:17:17+00:00","description":"The Oracle CREATE DATABASE LINK command lets you create a link between two databases and then query the tables and views in one from the other. READ MORE.","breadcrumb":{"@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#primaryimage","url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg","contentUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2015\/10\/oracle-create-database-link-guide.jpg","width":770,"height":340,"caption":"Oracle Create Database Link"},{"@type":"BreadcrumbList","@id":"https:\/\/dbadave.co.uk\/oracle-create-database-link-command\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbadave.co.uk\/"},{"@type":"ListItem","position":2,"name":"Oracle Create Database Link Command"}]},{"@type":"WebSite","@id":"https:\/\/dbadave.co.uk\/#website","url":"https:\/\/dbadave.co.uk\/","name":"DBA Dave - Oracle Training","description":"Expert DBA Tips & Advice","publisher":{"@id":"https:\/\/dbadave.co.uk\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbadave.co.uk\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbadave.co.uk\/#organization","name":"DBA Dave","url":"https:\/\/dbadave.co.uk\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/","url":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png","contentUrl":"https:\/\/dbadave.co.uk\/wp-content\/uploads\/2016\/02\/dba-dave-logo.png","width":170,"height":50,"caption":"DBA Dave"},"image":{"@id":"https:\/\/dbadave.co.uk\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/c51b3c9b3e2e2e9bbd141a719c024c40","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbadave.co.uk\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3d6ad3f291a149878281854f13654bf0?s=96&d=mm&r=g","caption":"admin"},"url":"https:\/\/dbadave.co.uk\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/233"}],"collection":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/comments?post=233"}],"version-history":[{"count":13,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/233\/revisions"}],"predecessor-version":[{"id":359,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/posts\/233\/revisions\/359"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/media\/357"}],"wp:attachment":[{"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/media?parent=233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/categories?post=233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbadave.co.uk\/wp-json\/wp\/v2\/tags?post=233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}