如何在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中文网其他相关文章！