Create Database Link Oracle
The creation of a database link in Oracle
straightforward. To create a database link in Oracle, specify the
database link name, the remote user to connect to, the password for the
remote user and the TNS service name for the database link connection:
create public
database link
mylink
connect to
remote_username
identified by
mypassword
using 'tns_service_name';
You can also create a database link in Oracle to
non-Oracle databases,
Francisco
Riccio describes the steps for a database link to MySQL:
Step 1: Your first step to creating a
database link in Oracle to a non-Oracle database is having installed the
TRANSPARENT GATEWAY (it comes in as one of the options when you install
Oracle).
Step 2: To create a database link in Oracle to a
non-Oracle database, you must have a user in the SQL Server.
Step 3: In the directory <ORACLE_HOME>\tg4msql\admin look for the
file inittg4msql.ora and have the following options:
HS_FDS_CONNECT_INFO="SERVER=name_server;DATABASE=name_db"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=user_sqlserver
HS_FDS_RECOVERY_PWD=pass_user_sqlserver
Step 4:
Configure the listener and add the following
(SID_DESC =
(PROGRAM = tg4msql)
(SID_NAME = MSQL)
(ORACLE_HOME = C:\oracle1)
)
)
and in our tnsnames.ora add
MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = servidor)(PORT = 1521))
)
(CONNECT_DATA =
(SID =
MSQL)
)
(HS=OK)
)
where HS set up as a
heterogonous service
Step 5: The last step to creating a
database link in Oracle to a non-Oracle database is to create a link to
sql server.
create database
link msql connect to user_sqlserver identified by password using 'MSQL';
Step 6: You can now use the database link to a foreign
database:
select * from
table@msql