Oracle Database - Database Link

Card Puncher Data Processing

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.

Usage

select * from mySchema.myTable@myDatabaseLink;

Management

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; 

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

Test

select * from all_tables@myDbLink;
select * from dual@myDbLink;

Documentation / Reference





Discover More
Owb Table Operator Data Rule Properties
OWB - Table

The Table operator enables you to source data from and load data into tables stored You can set Target Filter for Update, Target Filter for Delete, and Match By Constraint. When a data...
Card Puncher Data Processing
Oracle Database - Compile

Invalid objects are recompiled automatically on use. Database link The admin script: The UTL_RECOMP package recompiles...
Card Puncher Data Processing
Oracle Database - Distributed Transactions

A distributed database is a set of databases in a distributed system that can appear to applications as a single data source. A distributed transaction is a transaction that includes one or more statements...
Card Puncher Data Processing
Oracle Database - GLOBAL_NAMES parameter

GLOBAL_NAMES is a parameter which permit to enforce the database global naming. FALSE is the default setting for this parameter. If you set the GLOBAL_NAMES parameter to TRUE (enforces global naming),...
Card Puncher Data Processing
Oracle Database - Global Database Name (GLOBAL_NAME)

The Oracle Database (11g) software identifies a database by its global database name. A global database name consists of the database name and database domain. Usually, the database domain is the same...
Hs Odbc Excel Define Name
Oracle Database - How to read data through an ODBC connection with Gateway (Heterogeneous services)

This article is aimed to show how you can access data from oracle database via an odbc connection direct by using the gateway odbc agent (or program) generic connectivity. In this example, an odbc connection...
Card Puncher Data Processing
Oracle Database - Objects

Database object. Each database object is considered to either be: a schema object (ie owned by a user) or a “non-schema object”. See for plsql object type: Object in different namespace are...
Card Puncher Data Processing
Oracle Database - Schema Object

The schema Objects are logical structures of data stored physically in data file and categorized in a schema. private synonym Database objects that are owned by a user are schema...
Card Puncher Data Processing
SQL Plus - Describe

Describe lists the column definitions for a table, view, or synonym, or the specifications for a function or procedure. where: connect_identifier is a database link - connect identifier You...



Share this page:
Follow us:
Task Runner