Recently, we want to implement a business. We need to query the corresponding data in the Oracle Core database based on the table data in the SQL Server database, and then make statistics in the SQL Server database. therefore, we need to implement the DBlink from the SQL Server database to the Oracle database to pull the data out of the Oracle database.
Preparations: 1. the SQL Server database must be installed;2. An Oracle client must be available. The client version is no lower than that of the Oracle database;3. Configure the tnsnames. ora file (Path: $ ORACLE_HOME $ \ db_1 \ NETWORK \ ADMIN \ tnsnames. ora)
OrclDBLink = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2) (PORT = 1521 )) ) (CONNECT_DATA = (SERVICE_NAME = ORCL. SNDA. COM) ) )
4. test whether the tns configuration is successful. Use sqlplus name to log on in the command line window. If the configuration is successful, the tnsname. ora configuration is correct.
Create a connection Server 1. Configure on the general page ,:
2. Configure on the Security page ,:
Test Access to the Oracle database 1. SELECT * FROM DBLINK .. HR. EMPLOYEES The query object must be in uppercase. If you want to query a field in uppercase, the connection server is followed by two points [..]
2. SELECT * FROM Openquery (DBLINK, 'select * from hr. EMPLOYEES '); This method is 50% faster than the previous method, which is equivalent to directly connecting to Oracle.