To connect to Oracle from ArcGIS clients, install an Oracle client on the ArcGIS client machines, set environment variables that reference the Oracle client, and create a database connection from ArcGIS.
To publish ArcGIS Server web services that reference the data in the Oracle database, register the database connection file with your ArcGIS Server sites.
Install Oracle client software
Oracle client software must be installed on all ArcGIS client machines that will connect to the database. If you do not have the privileges to install software on the machines where ArcGIS clients are running, you'll need to have your IT department install and configure the Oracle client for you.
Install a release of the Oracle client application that is compatible with the release of the database to which you want to connect.
You can obtain the Oracle Instant, Runtime, or Administrator Client from Oracle, and install it on the client computer, following the directions in your Oracle documentation.
Use the following table to determine the appropriate Oracle client configuration to use:
| Installation combinations | Configuration to use |
|---|---|
ArcGIS Server (single-machine site) and Oracle are installed on the same server; ArcGIS Pro is installed on different machines. | Install a 64-bit Oracle client on all ArcGIS Pro machines, and set the PATH variable to the Oracle client home directory. You do not need to install an Oracle client on the server because files to connect are installed with the Oracle database management system. Ensure the LD_LIBRARY_PATH (Linux) or PATH (Microsoft Windows) system variable is set to the Oracle home directory. If you set a tnsname for the Oracle client, you must use the same name on the ArcGIS Server and ArcGIS Pro machines. |
ArcGIS Server is installed on one or more machines that are separate from the DBMS. ArcGIS Pro is installed on computers separate from both Oracle and ArcGIS Server. | Install a 64-bit Oracle client on all ArcGIS Pro machines, and set the PATH variable to the Oracle client home directory. Install the 64-bit Oracle client on all ArcGIS Server machines and ensure the PATH (Windows) or LD_LIBRARY_PATH (Linux) system variable is set to the Oracle client home directory. If you set a tnsname for the Oracle client, you must use the same name on the ArcGIS Server and ArcGIS Pro machines. |
ArcGIS Server and ArcGIS Pro are installed on the same server; Oracle is installed on a different server. | Install the 64-bit Oracle client on all ArcGIS Server machines. Both ArcGIS Server and ArcGIS Pro will use this client. Set the PATH system variable to the Oracle client home directory. |
ArcGIS Server, ArcGIS Pro, and Oracle are installed on one machine. | ArcGIS Server and ArcGIS Pro will use the 64-bit client files installed with the Oracle database management system to connect. Set the PATH variable to the Oracle client home directory. If your ArcGIS Server site includes additional servers, install the 64-bit Oracle client on each of the servers in the site, and set the PATH variable to the Oracle client home directory. |
Connect from ArcGIS Pro
After you install the Oracle client on the ArcGIS Pro computer, set the PATH environment variable to the location of the Oracle client and create a database connection.
Set the PATH variable
Set the PATH environment variable on the ArcGIS Pro machine to the location of the Oracle Client installation. See Microsoft Windows documentation if you need instructions for setting the PATH environment variable.
If ArcGIS Pro was open on the machine before you set the PATH variable, restart it to pick up the new setting.
Connect to the database
Add a database connection using the Database Connection dialog box or the Create Database Connection geoprocessing tool.
The following steps describe using the Database Connection dialog box to connect to Oracle.
- Open the Catalog pane in ArcGIS Pro.
- Right-click Databases and click New Database Connection.
- Choose Oracle from the Database Platform drop-down list.
- Either type the Oracle TNS name in the Instance text box, or provide one of the following Oracle Easy Connect strings:
- The name of the Oracle server/Oracle service name or ID
For example, if Oracle is installed on myserver and myosvc is the Oracle service name, type the following:
myserver/myosvc
- The name of the Oracle server:Oracle port number/Oracle service name or ID
In this example, Oracle is installed on myserver, is listening on port 60000, and myoservice is the Oracle service name.
myserver:60000/myoservice
- The URL of the Oracle server
The URL for the same instance shown in the last example would be the following:
//myserver:60000/myoservice
- The name of the Oracle server (You can use this if the Oracle listener on the server is configured to point to a default instance.)
- The IP address of the Oracle server/Oracle service name or ID
For example, if the address of the server is 10:10:10:10, and the Oracle service name is orasvc, type 10:10:10:10/orasvc.
For IPV6 addresses, place brackets around the address, for example, [4000:ab5:0:0:f666:d191:77f5:e2bd]/orasvc.
- The IP address of the Oracle server:Oracle port number/Oracle service name or ID
In this example, the IP address is 10:20:30:40, port is 59999, and the Oracle service is myomy1: 10:20:30:40:59999/myomy1.
An IPV6 address for the same port and service would look like the following: [6543:ef4:0:1:f587:l249:12f9:a3cd]:59999/myomy110:20:30:40:59999/myomy1.
Ensure your Oracle instance is configured to allow Easy Connect. If you have the full Oracle client installed but want to use Easy Connect syntax to connect, ensure the sqlnet.ora file on the client is configured to allow the use of Easy Connect and the Oracle server is configured to allow Easy Connect syntax. Also note that if your Oracle instance is not listening on the default Oracle port number, you must use connection syntax that includes the port number.
- The name of the Oracle server/Oracle service name or ID
- Choose the type of authentication to use when connecting to the database: Database authentication or Operating system authentication.
- If you choose Operating system authentication, you do not need to type a username and password—the connection is made using the login name and password used to sign in to the operating system. If the login used for the operating system is not a valid database login, the connection fails. Be aware that you cannot use operating system authentication if you are using the Oracle instant client.
Note:
When you use operating system authentication in Oracle, the operating system login is prefixed with an os_authent_prefix string (by default, OPS$) and stored in the USERNAME table. The maximum number of characters allowed for the username when connecting to the geodatabase is 30. In these specific cases, ArcGIS will place quotation marks around your username to pass it to the database. Those quotation marks count toward the 30-character total.
If you choose Database authentication, you must provide a valid database username and password in the User name and Password text boxes, respectively. Usernames can be a maximum of 30 characters.
Uncheck Save user name and password if you prefer not to save your login information as part of the connection; doing this can help maintain the security of the database. However, if you do this, you will be prompted to provide a username and password every time you connect.
Note:
Save user name and password must be checked for connection files that use database authentication and provide ArcGIS web services with access to the database, or if you want to search ArcGIS Pro to locate data accessed through this connection file.
- If you choose Operating system authentication, you do not need to type a username and password—the connection is made using the login name and password used to sign in to the operating system. If the login used for the operating system is not a valid database login, the connection fails. Be aware that you cannot use operating system authentication if you are using the Oracle instant client.
- Click OK to create the connection file.
A database connection appears under Databases in the Catalog pane, and a connection file (.sde) is created in the ArcGIS Pro project directory.
You can rename the file by typing a new name in the Catalog pane and pressing Enter.
Connect from ArcGIS Server
After you install a 64-bit Oracle client on all machines in the ArcGIS Server site, set variables, create a database connection file, grant privileges, and register the database with your ArcGIS Server site.
Set the PATH environment variable (Windows only)
Set the PATH environment variable on each ArcGIS Server Windows server to the location of the Oracle Client installation. See Microsoft Windows documentation if you need instructions for setting the PATH environment variable.
If ArcGIS Server was running before you configured the Oracle client and set the PATH variable, you must restart ArcGIS Server. You can restart ArcGIS Server from the Windows Services interface.
Create a database connection file
Create a database connection file using the Database Connection dialog box in ArcGIS Pro, as described above.
Alternatively, you can run the Create Database Connection tool in ArcGIS Pro or use Python to run the Create Database Connection command from an ArcGIS Server machine to create a database connection file (.sde) that connects to the database.
The following information is specific to creating a database connection file for use with ArcGIS Server:
- You must save the user information with the connection file.
- If you choose to use operating system authentication, use a domain account for the ArcGIS Server account and add the domain account to the Oracle database.
Grant privileges
The database user, role, or ArcGIS Server account (if you're using operating system authentication) with which you connect to the database must be granted privileges in the database to access the data to be published. The privileges and the circumstances under which you need to grant them are as follows:
| Privilege | Reason |
|---|---|
CREATE SESSION | Required to connect to the database. |
CREATE TABLE | Selection sets cause a log file table to be created in the geodatabase. If a user does not have this privilege, log file tables will be created in the sde user's schema. |
SELECT privileges on the datasets to be published | Grant the user or ArcGIS account at least SELECT privileges on the datasets to be published. |
INSERT, UPDATE, or DELETE privileges on data published to editable feature services | To edit data through a feature service, privileges sufficient to edit must be granted on the datasets. |
The database administrator must grant the first two privileges listed above. If the data is in a geodatabase, the data owner must grant the required privileges on the datasets. If the data is in a database, the data owner can grant privileges on datasets from ArcGIS or the database administrator can use database tools to grant the required privileges.
If you will be registering the geodatabase as a managed database for a stand-alone or federated ArcGIS Server site, the account with which you connect must have privileges to create data in the geodatabase.
See Privileges for geodatabases in Oracle or Privileges for using ArcGIS with an Oracle database for more information.
Register the database
To allow ArcGIS Server sites to access the data, use the database connection file you created to add a registered data store in ArcGIS Pro or add a data store item in the portal.