Perform a direct connection to an Oracle database and register Oracle Spatial data

Summary

For Esri products to perform a direct connection to an Oracle database and successfully register Oracle Spatial tables as ArcSDE layers, several steps must be taken:

· The Oracle client must be installed.
· An sde user must exist in the database and be granted the proper permissions.
· ArcSDE must be installed and a geodatabase created.
· A configuration keyword with the parameter GEOMETRY_STORAGE set to SDO_GEOMETRY must exist in the sde.DBTUNE table.
· Metadata must have been inserted for the Oracle Spatial tables to be registered.
· It is recommended a spatial index be created on the Oracle Spatial table.
· The ArcSDE sdelayer command should be used to register Oracle Spatial tables with ArcSDE.

Information and links for these steps are provided below.

Procedure

1. Install and configure the Oracle client software on each computer where an Esri ArcSDE client application is installed.
   Consult the Oracle documentation for the appropriate DBMS release for instructions on installing and properly configuring the Oracle client software. Read the topic Setting up a direct connection to Oracle.
2. Install ArcSDE on the server.
   Often, ArcSDE is installed on the same server as the Oracle DBMS, but it does not have to be.
   Instructions for installation of the ArcSDE component are described in the ArcSDE for Oracle installation guide provided on the ArcSDE installation media.
3. Create an sde user, create a tablespace for it, and grant permissions to allow the sde user to create the geodatabase.
   Modify and use the createsdeoracle.sql script to manually create the sde user, grant it permissions to create a geodatabase, and create a tablespace of at least 400MB. The createsdeoracle.sql script is located in the SDEHOME > tools > oracle directory.
4. Create other Oracle users who will create tables in the geodatabase. For these new users or for existing Oracle users who own Oracle Spatial tables to be registered with ArcSDE, grant the following permissions:
   CREATE SESSION
   CREATE SEQUENCE
   CREATE TABLE
   CREATE TRIGGER
   
   

   Note:
   A list of permissions for different types of database users can be found in the topic User permissions. For ArcSDE 9.1 and earlier releases, consult the ArcSDE for Oracle Configuration and Tuning Guide PDF file found on the ArcSDE installation media.

   
   
   In addition, the Oracle database administrator (DBA) must grant these object privileges to PUBLIC.
   
   

   Code:
   grant execute on dbms_pipe to public;
   grant execute on dbms_lock to public; 

   
   
5. Complete postinstallation setup to create a geodatabase in the Oracle database and license it.
   Postinstallation and licensing instructions are also provided in the ArcSDE for Oracle installation guide. If performing the postinstallation setup on Windows using the Post Installation wizard, select a Custom postinstallation and uncheck SDE User Environment and Create ArcSDE Service.
   
   On UNIX or Linux, or to run postinstallation manually on a Windows server, use the sdesetup command to create the geodatabase and authorize the geodatabase with the license. This command resides in the bin subdirectory of the application installation location, along with all other ArcSDE administration commands. Run sdesetup as the Oracle sde user.
   
   In ArcSDE 9.1 and earlier releases, the sdesetup command is database-specific. For example:
   
   

   Code:
   sdesetupora8i -o install -p <sde_user_password>@<oracle_instance>. 
   sdesetupora9i -o install -p <sde_user_password>@<oracle_instance>.

   
   
   In ArcSDE 9.2 and later releases, the command is generic and the database type is specified with the -d option. For example:
   
   

   Code:
   sdesetup -o install -d Oracle10g  <sde_user_password>@<oracle_instance>

   
   
   Consult the Administration Command Reference provided with the release of ArcSDE being used for information on the sdesetup command.
   
   

   Note:
   Esri does not support layers created with the Oracle tool shp2sdo. If layers have been created in this format, Oracle Spatial layers do not register properly. These layers must be removed or upgraded to the version of Oracle Spatial format supported for the release of ArcSDE being used before the Oracle Spatial layers register properly with the ArcSDE metadata. If layers created with shp2sdo exist in the database, it may cause sdesetup to fail. 

   
   
6. The sde.DBTUNE table must contain an SDO_GEOEMTRY configuration keyword.
   By default, when the geodatabase is created, the sde.DBTUNE table should contain a configuration keyword called SDO_GEOMETRY. When this configuration keyword is used, the data being loaded or created will use Oracle Spatial geometry storage.
   
   If the sde.DBTUNE table does not contain an SDE_GEOMETRY keyword, one can be added using the sdedbtune command. Read the ArcSDE Administration Command provided with the release of ArcSDE being used for instructions on using the sdedbtune command.
7. Add metadata to the Oracle Spatial ALL_SDO_GEOM_METADATA table.
   If metadata has not yet been inserted for the Oracle Spatial tables to be registered, do so now.
   For example:
   
   

   Code:
   insert into user_sdo_geom_metadata values ('myospattbl', 'geometry', 
   SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 0, 20, 0.05), SDO_DIM_ELEMENT
   ('Y', 0, 20, 0.05)), null);

   
   
   A common problem with registering Oracle Spatial tables with ArcSDE is that the metadata for each Oracle Spatial table contained in ALL_SDO_GEOM_METADATA is missing or incorrect. This data must be inserted when the Oracle Spatial table is created. If the table is created using Esri tools, the metadata is inserted by those tools. If the tables were created using another method, such as SQL, the user is required to supply the metadata, as indicated above.
   
   See the Oracle Spatial User Guide and Reference for an explanation of this metadata.
8. Create a spatial index on the SDO_GEOMETRY column of the Oracle Spatial table.
   Spatial index parameters cannot be specified when the table is registered with ArcSDE, so it is recommended that the spatial column of the table is indexed before registering it with ArcSDE.
   For example:
   
   

   Code:
   create index POLY_SPIDX on myospattbl(geometry) indextype is mdsys.spatial_index;
   

   
   
9. Use the sdelayer command with the register operation to register individual Oracle Spatial tables with ArcSDE.
   The following rules apply to successfully register existing Oracle Spatial tables with ArcSDE :
   · The table must be registered by the table owner (in other words, the user in whose schema the table exists).
   · The table can contain only one spatial column. If the table contains more than one, create a view of the table that contains only one spatial column and register it.
   · The table can contain only one column of a user-defined type.
   · The table must store only one type of geometry (e.g., point or line, but not both).
   · All records in the table must be registered with the same spatial reference ID (SRID).
   
   To make a direct connection using the sdelayer command, provide the direct connect syntax appropriate to the release of Oracle being used. Also, append the 'at' sign (@) followed by the Oracle database service name to the password. See ArcSDE connection syntax for syntax for each supported Oracle release.
   
   In this example, the table myospattbl is being registered with ArcSDE 9.2. The table contains an SDO_GEOMETRY column named geometry and stores polygons. The table resides on the server, quail, in an Oracle 10g database that has a service name of orasvc, and is owned by user fyodor. The -k option specifies the SDO_GEOMETRY keyword be used to register the table.
   

   Code:
   sdelayer -o register -l myospattbl,geometry -e a -C id,SDE -i sde:oracle10g:/;LOCAL=MYTNSNAME 
   -k SDO_GEOMETRY -s quail -u fyodor -p open.up@orasvc

   
   

   Note:
   Beginning with ArcSDE 9.3, the -t option must also be specified when registering tables. Consult the ArcSDE Administration Command Reference for information and examples of using the sdelayer command to register spatial tables with the appropriate release of ArcSDE.

   
   
   The topic Registering tables to be used by ArcGIS Desktop describes registering both spatial and nonspatial tables with ArcSDE and with the geodatabase.
10. All registered Oracle Spatial layers should now be visible to the user by way of an Esri client application. If all registered Oracle Spatial layers are not visible to the user by way of an Esri client application, check the file ARCHOME > etc. > sde.errlog or SDEHOME > etc. > sdedc_Oracle.log for information about why certain layers were not registered correctly.