{"id":2120,"date":"2025-01-16T17:08:07","date_gmt":"2025-01-16T17:08:07","guid":{"rendered":"https:\/\/w3buddy.com\/?p=2120"},"modified":"2026-01-15T13:19:13","modified_gmt":"2026-01-15T07:49:13","slug":"managing-database-links-in-oracle-a-step-by-step-guide","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/managing-database-links-in-oracle-a-step-by-step-guide\/","title":{"rendered":"Managing Database Links in Oracle: A Step-by-Step Guide"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">This guide walks you through creating, managing, and deleting database links (DBlinks) in Oracle. Database links enable connectivity between Oracle databases, allowing data sharing and querying.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Overview<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What is a Database Link?<\/strong> A database link is a schema object that defines a connection path to a remote database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Types of Database Links:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Private Database Link<\/strong>: Accessible only to the schema owner.<\/li>\n\n\n\n<li><strong>Public Database Link<\/strong>: Accessible to all users in the database.<\/li>\n\n\n\n<li><strong>Global Database Link<\/strong>: Defined in an OID or Oracle Names Server for network-wide use.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">To find the global database name, use:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT * FROM global_name;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2. Environment Setup<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Source Database Details<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Hostname<\/strong>: <code>source-db.example.com<\/code><\/li>\n\n\n\n<li><strong>DB Name<\/strong>: <code>source_db<\/code><\/li>\n\n\n\n<li><strong>Schema<\/strong>: <code>source_user\/source_password<\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>TNS Entry:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">source_db =\n  (DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = source-db.example.com)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = source_db)\n    )\n  )<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Target Database Details<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Hostname<\/strong>: <code>target-db.example.com<\/code><\/li>\n\n\n\n<li><strong>DB Name<\/strong>: <code>target_db<\/code><\/li>\n\n\n\n<li><strong>Schema<\/strong>: <code>target_user\/target_password<\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>TNS Entry:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">target_db =\n  (DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = target_db)\n    )\n  )<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">3. Add TNS Entry<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Add the target database TNS entry to the <code>tnsnames.ora<\/code> file on the source database:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">target_db =\n  (DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = target_db)\n    )\n  )<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">4. List Existing Database Links<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To view existing database links:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT * FROM dba_db_links;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If no links exist, you will see:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">no rows selected<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">5. Create a Public Database Link<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Public database links allow all users in the source database to access the target database.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Using TNS Entry<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">CREATE PUBLIC DATABASE LINK target_link\nCONNECT TO target_user IDENTIFIED BY target_password\nUSING 'target_db';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Without Modifying TNS Entry<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">CREATE PUBLIC DATABASE LINK target_link_no_tns\nCONNECT TO target_user IDENTIFIED BY target_password\nUSING '(DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = target_db)\n    )\n  )';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Using Easy Connect<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-\">CREATE PUBLIC DATABASE LINK target_link_easy\nCONNECT TO target_user IDENTIFIED BY target_password\nUSING 'target-db.example.com:1521\/target_db';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">6. Create a Private Database Link<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Private database links are accessible only to the schema owner.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Grant Privileges<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">GRANT CREATE DATABASE LINK TO source_user;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Using TNS Entry<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">CREATE DATABASE LINK private_link\nCONNECT TO target_user IDENTIFIED BY target_password\nUSING 'target_db';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Without Modifying TNS Entry<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">CREATE DATABASE LINK private_link_no_tns\nCONNECT TO target_user IDENTIFIED BY target_password\nUSING '(DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = target_db)\n    )\n  )';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Using Easy Connect<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">CREATE DATABASE LINK private_link_easy\nCONNECT TO target_user IDENTIFIED BY target_password\nUSING 'target-db.example.com:1521\/target_db';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">7. List Database Links Again<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Run the following query for detailed database link information:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SET LINES 180 PAGES 999\nCOLUMN owner FORMAT A15\nCOLUMN db_link FORMAT A15\nCOLUMN username FORMAT A15\nCOLUMN host FORMAT A39\nCOLUMN creation_date FORMAT A20\n\nSELECT owner, db_link, username, host,\n       TO_CHAR(created, 'MM\/DD\/YYYY HH24:MI:SS') AS creation_date\nFROM dba_db_links;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">8. Verify Database Links<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Verify Public Database Links<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT COUNT(*) FROM sample_table@target_link;       -- Using TNS entry\nSELECT COUNT(*) FROM sample_table@target_link_no_tns; -- Without TNS entry\nSELECT COUNT(*) FROM sample_table@target_link_easy;   -- Easy connect<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Verify Private Database Links<\/h3>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT COUNT(*) FROM sample_table@private_link;       -- Using TNS entry\nSELECT COUNT(*) FROM sample_table@private_link_no_tns; -- Without TNS entry\nSELECT COUNT(*) FROM sample_table@private_link_easy;   -- Easy connect<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">9. Drop Public Database Links<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To drop public database links, login as the owner and execute:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DROP PUBLIC DATABASE LINK target_link;\nDROP PUBLIC DATABASE LINK target_link_no_tns;\nDROP PUBLIC DATABASE LINK target_link_easy;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">10. Drop Private Database Links<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To drop private database links, login as the schema owner and execute:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DROP DATABASE LINK private_link;\nDROP DATABASE LINK private_link_no_tns;\nDROP DATABASE LINK private_link_easy;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">To confirm deletion:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT * FROM dba_db_links;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If no links exist, you will see:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">no rows selected<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Thank you for exploring this guide on managing Oracle database links. For further details and in-depth understanding, refer to the <a href=\"https:\/\/docs.oracle.com\/cd\/E18283_01\/server.112\/e17120\/ds_concepts002.htm\" target=\"_blank\" rel=\"noreferrer noopener\">official Oracle documentation on database links<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This guide walks you through creating, managing, and deleting database links (DBlinks) in Oracle. Database links enable connectivity between Oracle databases, allowing data sharing and querying. 1. Overview What is a Database Link? A database link is a schema object that defines a connection path to a remote database. Types of Database Links: To find [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-2120","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=2120"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2120\/revisions"}],"predecessor-version":[{"id":2121,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2120\/revisions\/2121"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=2120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=2120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=2120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}