如何在SQL中創建數據庫鏈接？

在Oracle中創建數據庫鏈接的步驟如下：1. 使用CREATE DATABASE LINK語句定義鏈接名稱、遠程用戶名密碼及連接字符串；2. 確保網絡可達並配置TNS或使用完整連接描述符；3. 具備CREATE DATABASE LINK權限；4. 可通過SELECT * FROM dual@link_name測試連接；5. 用DROP DATABASE LINK刪除鏈接。該操作支持跨數據庫訪問對象，需遵循安全規範。

Creating a database link in SQL allows you to access objects (like tables or views) from a remote database. The exact syntax and support depend on the database system you're using. Below is a guide focused on Oracle , which has robust support for database links. Other systems like PostgreSQL also support similar features, but Oracle is the most common context for this operation.

What is a Database Link?

A database link (DB Link) is a schema object that enables you to access tables, views, or run queries on a remote database as if you were connected directly to it.

Creating a Database Link in Oracle

1. Basic Syntax

 CREATE DATABASE LINK link_name
CONNECT TO remote_username IDENTIFIED BY remote_password
USING 'connect_string';

• link_name : The name you assign to the database link.
• remote_username/password : Credentials for the remote database user.
• connect_string : The TNS (Transparent Network Substrate) name or full connection descriptor for the remote database.

2. Example

Suppose you want to create a link to a remote database where:

• Remote user: scott
• Password: tiger
• TNS alias: REMOTE_DB

 CREATE DATABASE LINK remote_db_link
CONNECT TO scott IDENTIFIED BY tiger
USING 'REMOTE_DB';

After creating the link, you can query remote tables like:

 SELECT * FROM employees@remote_db_link;

Types of Database Links

• Private : Owned by a specific user and accessible only to that user.
• Public : Accessible to all users in the local database.
• Shared : Allows multiple sessions to reuse the same connection.

To create a public database link, add the PUBLIC keyword:

 CREATE PUBLIC DATABASE LINK remote_public_link
CONNECT TO scott IDENTIFIED BY tiger
USING 'REMOTE_DB';

Prerequisites

Before creating a database link, ensure:

• The remote database is accessible over the network.
• The TNS entry ( REMOTE_DB in the example) is configured in tnsnames.ora , or use a full connection string.
• The user has CREATE DATABASE LINK privilege.

You can grant the privilege like this:

 GRANT CREATE DATABASE LINK TO your_user;

Using a Full Connection String (No TNS)

If you don't want to use a TNS name, you can embed the connection details:

 CREATE DATABASE LINK remote_db_link
CONNECT TO scott IDENTIFIED BY tiger
USING '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=remote_host)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=orcl)))';

Testing the Link

After creation, test it with a simple query:

 SELECT * FROM dual@remote_db_link;

If it returns a row, the link is working.

Dropping a Database Link

To remove a link:

 DROP DATABASE LINK remote_db_link;

-- For public link
DROP PUBLIC DATABASE LINK remote_public_link;

Notes for Other Databases

• PostgreSQL : Uses postgres_fdw (Foreign Data Wrapper) to link to other PostgreSQL databases.
• SQL Server : Uses linked servers via sp_addlinkedserver .
• MySQL : No native database link support; use FEDERATED engine (limited) or application-level joins.

In Oracle, database links are powerful but should be used carefully—especially with stored credentials. Always follow security best practices like restricting privileges and auditing usage.

Basically, it's about connecting across databases securely and efficiently.

以上是如何在SQL中創建數據庫鏈接？的詳細內容。更多資訊請關注PHP中文網其他相關文章！