Oracle Database Links

最新推荐文章于 2022-05-24 11:57:34 发布

转载最新推荐文章于 2022-05-24 11:57:34 发布 · 113 阅读

本文详细介绍Oracle数据库链接的创建及管理方法，包括使用不同用户身份创建数据库链接、关闭及删除数据库链接等操作。此外还提供了如何跨数据库链接查询数据的示例。

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 all_tables@remote_db
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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/167606/viewspace-876331/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/167606/viewspace-876331/