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