Oracle数据库链接创建求助：从USER1跨库访问USER3表

Let’s walk through this step by step, aligned with your specific scenario:

• You have access to USER1 in DATABASE1
• In DATABASE2, you hold credentials for USER2, which can query partial tables in USER3’s schema
• Your goal: Build a database link in USER1 to directly access those USER3 tables

Prerequisite Check #

First, confirm that USER2 already has SELECT permissions on the USER3 tables you need to access (you mentioned you can query them via USER2, so this should be set up already). If not, ask a DBA in DATABASE2 to run this for each target table:

GRANT SELECT ON USER3.your_target_table TO USER2;

Step 1: Gather Connection Details for DATABASE2 #

You’ll need either:

• A TNS alias for DATABASE2 (configured in your tnsnames.ora file), or
• Full connection parameters: hostname, port, and service name/SID of DATABASE2
• USER2’s username and password

Step 2: Create the Database Link in USER1 (DATABASE1) #

First, make sure USER1 has permission to create a database link. If not, ask your DBA to run:

GRANT CREATE DATABASE LINK TO USER1;

Choose one of the following methods to create the link:

Method 1: Using TNS Alias

If your environment uses a pre-configured TNS alias (e.g., DB2_TNS), run this as USER1:

CREATE DATABASE LINK db2_user2_link
CONNECT TO USER2 IDENTIFIED BY 'user2_password'
USING 'DB2_TNS';

Method 2: Using Direct Connection String

If you don’t have a TNS alias, use the full connection details directly:

CREATE DATABASE LINK db2_user2_link
CONNECT TO USER2 IDENTIFIED BY 'user2_password'
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db2_host)(PORT=db2_port))(CONNECT_DATA=(SERVICE_NAME=db2_service)))';

Replace db2_host, db2_port, and db2_service with your actual DATABASE2 values.

Step 3: Query USER3’s Tables via the Link #

Once the link is created, access USER3’s tables by specifying the schema and link name:

SELECT * FROM USER3.your_target_table@db2_user2_link;

Swap your_target_table with the actual table name you need to query.

Key Notes #

• If you want the link accessible to all users in DATABASE1, use CREATE PUBLIC DATABASE LINK instead (you’ll need the CREATE PUBLIC DATABASE LINK permission for this)
• Double-check network connectivity between DATABASE1 and DATABASE2 (firewall rules, listener settings) if you hit connection errors
• For better security, avoid hardcoding passwords in scripts—consider using Oracle Wallet (or your database’s equivalent) to store credentials

内容的提问来源于stack exchange，提问作者Sunil Kumar