To enable oracle to directly call tables and views in sql server, oracle 11g transparent gateway can implement this function.

1、 Download oracleGetway

Download address: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

2、 The transparent gateway can be installed on the server where the sql server is located by decompressing and installing it directly on the Windows machine.

3、 Modify the configuration file after installation

First profile location: D:\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora

The path is found according to the path you installed

Key configurations:

 HS_FDS_CONNECT_INFO=[10.0.3.152]//xmgxy     #The SQL server database IP address and database name will be configured during installation HS_FDS_TRACE_LEVEL=OFF #Log tracking, which can be set to ON, will generate log tracking HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER

Second profile location: D:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora

Add the following:

 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=xmgxy) # sqlserver database name (PROGRAM=dg4msql) # dg4msql This is D:  product  11.2.0  tg_1  dg4msql This directory name is generally unchanged (SID_NAME=dg4msql) # SID_NAME is D:  product  11.2.0  tg_1  dg4msql  admin  initdg4msql.ora The file removes the symbol of init and is also the name of the connected instance (ORACLE_HOME = D:\product\11.2.0\tg_1) ) )

4、 Start listening

Start listening location: D:\product\11.2.0\tg_1\BIN\LSNRCTL. EXE

Directly open, stop first and then open

stop

start that will do

You can see the instance dg4msql Started

5、 Open the windows firewall, the default port is 1521, and the port is released in the inbound rules

6、 Oracle server operation

--The dblink statement for creating tables is as follows: the dblink connection name is ykt_dblink, the account number is sa, and the password is 123456. You can also write this file into the oracle listening file

 create public database link ykt_dblink connect to sa identified by "123456" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.0.3.152)(PORT = 1521)) (CONNECT_DATA=(SID=dg4msql)) # This SID is the SID_NAME of the configuration file, that is, the instance name (HS=OK) )'

--View the dblink statement

select t.* from dba_objects t where object_type='DATABASE LINK';

--Verify connection:

Select * from sqlserverXXX table @ dblink connection name

For example: select * from m_ykt_xfjl@ykt_dblink

Note: You must use "" to query a field, or an error "invalid identifier" will be reported

Reference article:
https://www.cnblogs.com/xqzt/p/5688659.html
https://jingyan.baidu.com/article/dca1fa6f42a662f1a44052a4.html