Oracle database links: create, list and query remote objects
Create a database link, list the links defined in the database and query a remote table or view with the @link syntax.
A database link lets a session in one Oracle database query objects in another one. This article covers creating private and public links, listing them, querying a remote object with the @link suffix, and copying remote data into a local table. Names below (db_remote, remote_user, remote_tns_alias) are examples.
Create a private database link
CREATE DATABASE LINK db_remote
CONNECT TO remote_user IDENTIFIED BY VALUES '<encrypted_password>'
USING 'remote_tns_alias';
IDENTIFIED BY VALUES is the form returned by DBMS_METADATA when it extracts an existing link; it takes the encrypted password. To create a link from a clear-text password, use IDENTIFIED BY '<password>'.
Create a public database link
CREATE PUBLIC DATABASE LINK public_remote
CONNECT TO remote_user IDENTIFIED BY '<password>'
USING 'remote_tns_alias_2';
A public link is available to every user of the database.
Query a remote object
Add @link_name after the object name:
SELECT * FROM remote_table@db_remote;
SELECT * FROM app_schema.component@db_remote;
Copy remote data into a local table:
CREATE TABLE local_copy AS (SELECT * FROM app_schema.remote_view@public_remote);
List the database links
SELECT DB_LINK, USERNAME, HOST FROM ALL_DB_LINKS; -- links the current user can use
SELECT DB_LINK, USERNAME, HOST FROM DBA_DB_LINKS; -- all links in the database
SELECT DB_LINK, USERNAME, HOST FROM USER_DB_LINKS; -- links owned by the current user
Check the session and the connection context
select USER from dual;
set linesize 2000
SELECT sys_context ('USERENV','db_name') db_name,
sys_context ('USERENV','server_host') server_host,
sys_context ('USERENV','current_schema') current_schema,
sys_context ('USERENV','session_user') current_user,
sys_context ('USERENV','host') client_host,
sys_context ('USERENV','ip_address') client_ip_address
FROM dual;
Change the current schema
ALTER SESSION SET CURRENT_SCHEMA changes the schema used to resolve unqualified object names. It does not change the user that is connected.
CONNECT system
ALTER SESSION SET CURRENT_SCHEMA = system;
SELECT * FROM emp;