Database link from
Oracle to SQL Server
Oracle Database Tips by Donald Burleson
Question: How do I
create a database link from Oracle to SQL Server. I need to
link to SQL Server as part of a band-aid before migrating everything
Answer: Oracle heterogeneous
services allow you to define a database link between Oracle and SQL Server, as
well as links to DB2 and other inferior databases.
complete notes in creating a database link between Oracle and SQL Server.
1. Install Oracle ODBC drivers on the
server (your local Oracle database) that will access the remote SQL Server
database using the database link.
2. Setup the ODBC connection on the local Oracle database using the Windows
ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the SQL
4. Ensure that your global_names parameter is set to False.
5. Configure the Oracle Heterogeneous services by creating an initodbc.ora
file within the Oracle database.
7. Modify the Listener.ora file.
SID_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells Oracle to use heterogeneous services.
(SID_NAME=Cas30C) -- Enter the DSN on this line
(ORACLE_HOME = c:\oracle10gdb) -- Enter your
Oracle home on this line
(PROGRAM = hsodbc)
8. Modify the Tnsnames.ora file. This is
the Oracle database installation accessed by the users to connect to the
-- (Server x)
(PORT=1521))) -- Enter the port on which the
server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to
use hetergeneous services
9. Reload the listener on local Oracle
10. Create a database link on the local Oracle installation that accesses
the heterogeneous connection, which, in turn, connect to SQL Server.
11. Run a SQL Server Select statement from the Oracle installation using the
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.