oracle database links,Oracle Database Links

Data Dictionary Objects Related To Database Links

link$

user_db_links

all_db_links

dba_db_links

v_$dblink

v_$session_connect_info

[@more@]

Data Dictionary Objects Related To Database Links

link$

user_db_links

all_db_links

dba_db_links

v_$dblink

v_$session_connect_info

System Privileges Related To Database Links

create database link

create public database link

drop public database link

Init.ora parameters related to Database Links

dblink_encrypt_login

global_names (required to be TRUE for replication)

open_links

open_links_per_instance

Notes:

The single quotes around the service name are mandatory

The service name must be TNSNAMES.ORA file on the server

1.Create Database Link

DB Link - Current User

CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER

USING '';

比如：

CREATE DATABASE LINK test_link

CONNECT TO CURRENT_USER

USING 'lab';

DB Link - Connected User

CREATE [PUBLIC] DATABASE LINK USING '';

比如：

CREATE PUBLIC DATABASE LINK test_link

USING 'LAB';

DB Link - Fixed User

CREATE [PUBLIC] DATABASE LINK CONNECT TO IDENTIFIED BY USING '';

比如：

CREATE DATABASE LINK test_link

CONNECT TO abc

IDENTIFIED BY abc

USING 'orabase';

Fixed User Caution

SELECT db_link, username, password, host, created

FROM user_db_links;

Close Database Link

Close Link ALTER SESSION CLOSE DATABASE LINK ;

ALTER SESSION CLOSE DATABASE LINK test_link;

Drop Database Link

Drop Standard Link

DROP DATABASE LINK ;

DROP DATABASE LINK test_link;

Drop Public Link

DROP PUBLIC DATABASE LINK ;

DROP PUBLIC DATABASE LINK test_link;

Database Link Security

View Password

SELECT db_link, password FROM user_db_links;

Encrypt Link Password

Set the init.ora parameter dblink_encrypt_login to TRUE then restart the database

Querying Across Database Links

Hint

By default Oracle selects the site, local or remote, on which to perform the operation. A specific site can be selected by the developer using the DRIVING_SITE hint.

Test Link

BEGIN

ALTER SESSION CLOSE DATABASE LINK remove_db;

SELECT table_name

INTO i

FROM

WHERE rownum = 1;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20999, 'No Cnxn');

END;

Database Links Querying

Test DB Link SELECT db_link, owner, host, username

FROM dba_db_links

ORDER BY db_link;

by outdo