Oracle Database - Database Link

> Database > Oracle Database

1 - About

A database link describes how one database instance can log in to another database instance.

A schema object in one database that enables users to access objects on a different database.

Advertising

3 - Usage

SELECT * FROM mySchema.[email protected];

4 - Management

4.1 - Syntax

CREATE [SHARED] [PUBLIC] DATABASE LINK dblink ... [USING 'net_service_name'];

Oracle Database - Net Service Name (tnsname)

CREATE DATABASE LINK warehouse USING 'warehouse'; 
SELECT DB_LINK FROM USER_DB_LINKS; 

4.2 - Privilege

Privilege Database Required for
CREATE DATABASE LINK Local Creation of a private database link
CREATE PUBLIC DATABASE LINK Local Creation of a public database link
CREATE SESSION Remote Creation of any type of database link
SELECT DISTINCT PRIVILEGE AS "Database Link Privileges"
FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
                     'CREATE PUBLIC DATABASE LINK')
Database Link Privileges
----------------------------------------
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE SESSION
Advertising

5 - Test

SELECT * FROM [email protected];
SELECT * FROM [email protected];

6 - Documentation / Reference

db/oracle/database_link.txt · Last modified: 2017/09/06 19:27 by gerardnico