{"id":13703,"date":"2024-03-15T07:44:33","date_gmt":"2024-03-15T07:44:33","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/"},"modified":"2025-08-05T22:15:50","modified_gmt":"2025-08-05T22:15:50","slug":"how-to-achieve-cross-database-join-queries-in-oracle","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/","title":{"rendered":"Oracle Cross-Database Joins: Complete Guide"},"content":{"rendered":"

In Oracle, there are several methods available to achieve cross-database joins.<\/p>\n

    \n
  1. Create a Database Link: First, create a database link in the target database, and then use this link to execute queries in the current database. For example, if you have two databases, A and B, and need to query data from database B in database A, you can create a database link in database A that points to database B, and then use this link to execute the query.<\/li>\n<\/ol>\n
    -- \u5728\u6570\u636e\u5e93A\u4e2d\u521b\u5efa\u6570\u636e\u5e93\u94fe\u63a5<\/span>\r\nCREATE<\/span> DATABASE LINK B_LINK CONNECT<\/span> TO<\/span> username IDENTIFIED BY<\/span> password USING<\/span> 'B'<\/span>;\r\n\r\n-- \u5728\u6570\u636e\u5e93A\u4e2d\u4f7f\u7528\u94fe\u63a5\u67e5\u8be2\u6570\u636e\u5e93B\u7684\u6570\u636e<\/span>\r\nSELECT<\/span> *<\/span> FROM<\/span> table<\/span>@B_LINK<\/span>;\r\n<\/code><\/pre>\n
      \n
    1. Using External Table: Create an external table in the target database, map the data from the target database to the external table, and then query the external table in the current database to retrieve the data from the target database. For example, if there are two databases A and B, and you need to query the data from database B in database A, you can first create an external table in database B pointing to the target table, and then query the external table in database A.<\/li>\n<\/ol>\n
      -- \u5728\u6570\u636e\u5e93B\u4e2d\u521b\u5efa\u5916\u90e8\u8868<\/span>\r\nCREATE<\/span> TABLE<\/span> external_table (\r\n  column1 datatype,\r\n  column2 datatype,\r\n  ...\r\n)\r\nORGANIZATION EXTERNAL<\/span> (\r\n  TYPE ORACLE_LOADER\r\n  DEFAULT<\/span> DIRECTORY directory_name\r\n  ACCESS PARAMETERS (\r\n    RECORDS DELIMITED BY<\/span> newline\r\n    FIELDS TERMINATED BY<\/span> ','<\/span> (\r\n      column1,\r\n      column2,\r\n      ...\r\n    )\r\n  )\r\n  LOCATION ('data_file.csv'<\/span>)\r\n)\r\nREJECT LIMIT UNLIMITED;\r\n\r\n-- \u5728\u6570\u636e\u5e93A\u4e2d\u67e5\u8be2\u6570\u636e\u5e93B\u7684\u6570\u636e<\/span>\r\nSELECT<\/span> *<\/span> FROM<\/span> external_table;\r\n<\/code><\/pre>\n
        \n
      1. Using a Union All query: merge the data from tables in two databases by using a Union All operation, and then query the merged result in the current database. For example, if we have databases A and B, and need to query the data from database B in database A, we can first query the tables in database A, and then merge the tables in database B using a Union All operation into the query result.<\/li>\n<\/ol>\n
        -- \u5728\u6570\u636e\u5e93A\u4e2d\u67e5\u8be2\u6570\u636e\u5e93A\u7684\u6570\u636e<\/span>\r\nSELECT<\/span> *<\/span> FROM<\/span> tableA\r\n\r\nUNION<\/span> ALL<\/span>\r\n\r\n-- \u67e5\u8be2\u6570\u636e\u5e93B\u7684\u6570\u636e<\/span>\r\nSELECT<\/span> *<\/span> FROM<\/span> tableB;\r\n<\/code><\/pre>\n
        It is important to note that the database connections and external tables mentioned above need to be created and configured in the corresponding database, and require the appropriate access permissions between databases. Additionally, cross-database join queries may have an impact on system performance, so it is recommended to consider performance and security factors in practical applications.<\/p>\n","protected":false},"excerpt":{"rendered":"
        In Oracle, there are several methods available to achieve cross-database joins. Create a Database Link: First, create a database link in the target database, and then use this link to execute queries in the current database. For example, if you have two databases, A and B, and need to query data from database B in […]<\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[18310,18312,18308,18309,18311],"class_list":["post-13703","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-oracle-cross-database-query","tag-oracle-database-connection","tag-oracle-database-link","tag-oracle-join-databases","tag-oracle-remote-join"],"yoast_head":"\nOracle Cross-Database Joins: Complete Guide - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn how to perform cross-database joins in Oracle using database links. Step-by-step guide included.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Cross-Database Joins: Complete Guide\" \/>\n<meta property=\"og:description\" content=\"Learn how to perform cross-database joins in Oracle using database links. Step-by-step guide included.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T07:44:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-05T22:15:50+00:00\" \/>\n<meta name=\"author\" content=\"Liam\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Liam\" \/>\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:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/\"},\"author\":{\"name\":\"Liam\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671\"},\"headline\":\"Oracle Cross-Database Joins: Complete Guide\",\"datePublished\":\"2024-03-15T07:44:33+00:00\",\"dateModified\":\"2025-08-05T22:15:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/\"},\"wordCount\":304,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"Oracle cross-database query\",\"Oracle database connection\",\"Oracle database link\",\"Oracle join databases\",\"Oracle remote join\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/\",\"name\":\"Oracle Cross-Database Joins: Complete Guide - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T07:44:33+00:00\",\"dateModified\":\"2025-08-05T22:15:50+00:00\",\"description\":\"Learn how to perform cross-database joins in Oracle using database links. Step-by-step guide included.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Cross-Database Joins: Complete Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671\",\"name\":\"Liam\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g\",\"caption\":\"Liam\"},\"sameAs\":[\"http:\/\/Wilson\"],\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/liamwilson\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle Cross-Database Joins: Complete Guide - Blog - Silicon Cloud","description":"Learn how to perform cross-database joins in Oracle using database links. Step-by-step guide included.","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:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Cross-Database Joins: Complete Guide","og_description":"Learn how to perform cross-database joins in Oracle using database links. Step-by-step guide included.","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T07:44:33+00:00","article_modified_time":"2025-08-05T22:15:50+00:00","author":"Liam","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Liam","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/"},"author":{"name":"Liam","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671"},"headline":"Oracle Cross-Database Joins: Complete Guide","datePublished":"2024-03-15T07:44:33+00:00","dateModified":"2025-08-05T22:15:50+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/"},"wordCount":304,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["Oracle cross-database query","Oracle database connection","Oracle database link","Oracle join databases","Oracle remote join"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/","name":"Oracle Cross-Database Joins: Complete Guide - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T07:44:33+00:00","dateModified":"2025-08-05T22:15:50+00:00","description":"Learn how to perform cross-database joins in Oracle using database links. Step-by-step guide included.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-achieve-cross-database-join-queries-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle Cross-Database Joins: Complete Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671","name":"Liam","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g","caption":"Liam"},"sameAs":["http:\/\/Wilson"],"url":"https:\/\/www.silicloud.com\/blog\/author\/liamwilson\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/13703","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=13703"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/13703\/revisions"}],"predecessor-version":[{"id":157705,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/13703\/revisions\/157705"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=13703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=13703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=13703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}