How to access an ORACLE table in SQL SERVER

In SQL server, can I access ORACLE tables and perform query operations? The answer is no doubt. The following describes how to access the ORACLE table in SQL SERVER for your reference.

I. Configuration

Method 1: directly connect to the oracle database through the SQL * net client of Oracle

1. Install the Oracle client on the SERVER where SQL SERVER is located, configure the client, and use sqlplus
Connect tools to Oracle databases
2. Open enterprise manager, choose security> link server, right-click and choose "New link server"
3. "linked server"-> ORCL (name the linked server)
Select "other data sources" and select "Microsoft ole db Provider" in the "Provider name" column.
For Oracle"
4. Choose "Product Name"> "Oracle" "Data Source"> "ora9i" (enter the service name in tnsnames. ora)
Choose "provider string"> "MADAORA"
5. Switch to the "Security" Page, add the username of the oracle database to "Local Logon", and select "use this security
Enter the user and password for logging on to the oracle database.
6. Switch to the "server options" Page and select all options.
7. Click "OK" to complete the settings.
8. Use the created linked server ORCL in the query Analyzer

SELECT * from orcl... SMS. EMP
Here, ORCL is the name of the linked server, SMS is the username of the Oracle database, and EMP is the table name.

Method 2: connect to the Oracle database through ODBC

1. Install the Oracle client on the SERVER where SQL SERVER is located, configure the client, and use sqlplus
Connect tools to Oracle databases
Management tools> Create a system DSN in the data source
2. Open enterprise manager, choose security> link server, right-click and choose "New link server"
3. "linked server"-> ORCL (name the linked server)
Select "other data sources" and select "Microsoft ole db Provider" in the "Provider name" column.
For ODBC"
4. Choose "Product Name"> "Oracle" "Data Source"> "ora9i" (enter the name of the created system DSN here)
Choose "provider string"> "MADAORA"
5. Switch to the "Security" Page, add the username of the oracle database to "Local Logon", and select "use this security
Enter the user and password for logging on to the oracle database.
6. Switch to the "server options" Page and select all options.
7. Click "OK" to complete the settings.
8. Use the created linked server ORCL in the query Analyzer
SELECT * from orcl... SMS. EMP
Here, ORCL is the name of the linked server, SMS is the username of the Oracle database, and EMP is the table name.

Ii. Questions about the inability to use indexes in queries

Query a table in SQL Server using the SQL Server cmdles statement:
SELECT * from orcl... SMS. emp where name = 'Tom'
EMP has millions of data records and indexes on the NAME. It is found that the query is very slow.
View v $ sqlarea, found that the WHERE condition was not passed to the oracle server, and then passed several tests
And finally solved the problem, summarized as follows:

When you use a linked server to access an ORACLE table and query an oracle table, if the field in the where clause is of the number type
The where condition will not be passed to the oracle server;

If it is char type, it can be uploaded to the oracle server, but it must be required when creating a linked server
Select the option "compatible with sorting rules", otherwise it will not be uploaded to the oracle server;

Use the Oracle to_date () function

Oracle memory structure-SGA

Oracle connect role permission changes

Actual permissions of Oracle system users

Oracle read-only user role creation