A quick tour of setting up a geodatabase in Oracle

This topic applies to ArcGIS for Desktop Standard and ArcGIS for Desktop Advanced only.

Use the Create Enterprise Geodatabase geoprocessing tool, Enable Enterprise Geodatabase geoprocessing tool, or a Python script to create a geodatabase in an Oracle database.

The following are some suggestions as to when you would use each method:

• Use the Create Enterprise Geodatabase geoprocessing tool if you have access to ArcGIS for Desktop (Standard or Advanced), know the Oracle sys password, want the tool to create the sde user and its default tablespace (400 MB), and want the tool to grant the sde user the privileges required to create a geodatabase, kill database connections, and upgrade a geodatabase.
• Run a Python script that calls the CreateEnterpriseGeodatabase function if you need to create the geodatabase from a computer running ArcGIS Engine with the Geodatabase Update extension or ArcGIS for Server (Standard or Advanced), and you want ArcGIS to create the sde user and its default tablespace (400 MB) and grant it the privileges required to create a geodatabase, kill database connections, and upgrade a geodatabase.
• Use the Enable Enterprise Geodatabase geoprocessing tool if you have access to ArcGIS for Desktop (Standard or Advanced) but you do not want to use the default settings the Create Enterprise Geodatabase tool uses, or you do not have the Oracle sys user password. For example, the database administrator (DBA) is not the same person who creates the geodatabase, he or she may want to create the database, sde user, and tablespace ahead of time, and grant the sde user the minimum privileges required to create a geodatabase. Then the geodatabase administrator can connect as the sde user and enable geodatabase functionality in the database.
• Run a Python script that calls the EnableEnterpriseGeodatabase function if you need to create the geodatabase from a computer running ArcGIS Engine with the Geodatabase Update extension or ArcGIS for Server (Standard or Advanced) but you do not want to use the default settings the Create Enterprise Geodatabase tool uses, or you do not have the Oracle sys user password.

Before you create a geodatabase, you must install Oracle and configure the DBMS. Configuring the DBMS involves setting up storage spaces for tables, indexes, and files that are stored in the database instance and adequately allocating memory. You should give consideration to the names, types, and locations of tables, indexes, and storage spaces you create; how large to make your backup log files; and what size to make the temporary space.

Check the system requirements on the Resource Center before you set up a geodatabase to be sure the database, ArcGIS, and hardware combinations you want to use are supported.

Setting up a geodatabase in Oracle

The following is a summary of the steps you take to set up a geodatabase in Oracle:

1. Install and configure Oracle.

   Protocol at your site will determine if the administrator or another staff member, such as an information systems technician, will do this. See your Oracle documentation for instructions.

2. Install the Oracle client application on the ArcGIS client computers.

   This must be done before a geodatabase can be created.

3. Create a geodatabase by doing one of the following:
   • Run the Create Enterprise Geodatabase geoprocessing tool from ArcGIS for Desktop (Standard or Advanced) to create an sde user, default tablespace, and a geodatabase.
   • Run a Python script from a computer running ArcGIS for Desktop (Standard or Advanced), ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server Enterprise (Standard or Advanced). See Scripting geodatabase creation in Oracle for samples. Depending on which script you run, you might need to create the sde user and tablespace prior to running the script.
   • Manually create an sde user and default tablespace in your Oracle database, grant the sde user privileges to create a geodatabase, then connect to the Oracle database from ArcGIS for Desktop (Standard or Advanced) as the sde user and enable geodatabase behavior. See Enabling geodatabase functionality in an Oracle database for instructions.
4. Optionally, If you plan to write SQL queries against ST_Geometry data in the geodatabase in Oracle, you must place the ST_Geometry library on your Oracle server in a place where the Oracle extproc has access to it.

   The ST_Geometry library can be found in the DatabaseSupport directory of your ArcGIS client. If Oracle is installed on a Linux or UNIX server, place the libst_shapelib.so file appropriate for your server operating system in a directory on the Oracle server. If Oracle is installed on a Windows server, place the Windows st_shapelib.dll file in a directory on your Oracle server.

   The directory on the server where you place the library must be accessible from the client machine from which you will create the geodatabase. If placing the library on a UNIX or Linux machine, be sure the database administrator has read access to the folder and execute privileges on the library file.

5. Additionally, if you plan to write SQL queries against ST_Geometry data in the geodatabase in Oracle, you must configure the Oracle extproc, and update the Oracle library entries on the Oracle server. Libraries are updated by running a CREATE OR REPLACE LIBRARY SQL statement. For example, on a Windows server where st_shapelib.dll has been placed in a folder named libraries, the statement would be:

   CREATE OR REPLACE LIBRARY st_shapelib AS
   'c:\libraries\st_shapelib.dll';
   

   On a Linux or UNIX server with a directory named myfiles, the statement would be:

   CREATE OR REPLACE LIBRARY st_shapelib AS
   '/usr/myfiles/libst_shapelib.so';
   

Alternate setup

There are some scenarios that require you to use additional tools to complete your geodatabase setup, such as when you want to use an ArcSDE service for connections or multiple geodatabases are needed in the same Oracle database. See Multiple geodatabases in Oracle for information on using multiple geodatabases in Oracle.

To use an ArcSDE service, you must install and configure the ArcSDE application server. See the installation guide provided with the ArcSDE application server for instructions for this scenario. The ArcSDE application server installation can be downloaded from the Esri Customer Care portal.