Thursday, October 1, 2009

Create Oracle Database Link access SQL Server

Just finished a project: customer A use Oracle Database 10G, customer B user SQL Server 2005.
Need read SQL Server from Oracle Database.

Here is how:

Request: Microsoft Windows 32 bit OS
Memory Great than 2 Giga-Byte

1. First Download oracle for windows from
Oracle Database 10g Release 1 ( for Microsoft Windows

2. Download Oracle Database 10g Release 1 patch for Microsoft Windows
3. Uncompress files
4. Start Installation
5. “Oracle Transparent Gateway for Microsoft SQL Server” has to bee install

(Manual attached: b14270.pdf)

6. Create a Database for convert (SID name = ora)
7. Notice the “Database Character Set” to
8. Create a Listener for connection
9. Setup MS SQL Server (Host Name and Database Name)
10. create a user in SQL Server for Oracle connection (User Name: cyx Password:testora)
11. Create a user in Oracle Server for operation (User Name: cyx Password: cyx)
12. Setup 4 configure files:

tnsnames.ora /listener.ora /inittg4msql.ora /tg4msql_tx.sql
(Files attached)

13. Create Database Link in Oracle:

create database link msql connect to "cyx" identified by "testora" using 'tg4msql';

14. Create Mirror Table in Oracle:

create table t2008_test
select *
from t2008_geo@msql;

15. Export Mirror Talbe by using command “exp”:

set ORACLE_SID=ora
exp cyx/cyx file=test.dmp table=t2008_test

PS:For save some memory and 3 services has been set to Manual:
OracleCSService /OracleORACLE_HOMETNSListener /OracleServiceORA
Please start these 3 services before your operation.

For More info and screen shoot please left commit and let me know.


  1. Hello, I enjoy reading all of your post. I wanted
    to write a little comment to support you.

  2. Thank you my friend.