Creating Oracle Schema to load data into ESRI ArcSDE

ESRI’s ArcSDE and Oracle Database combination can be tricky sometimes. Here are the steps to create an Oracle Schema and load data into ESRI’s ArcSDE. These steps should be performed after setting up ArcSDE software and running the post install and assuming that the default ArcSDE service is already running in port 5151.

First the Oracle Database and the user must be created. Next the proper permissions have to be provided.

Finally in ArcSDE we need to run the setup script then all is done.

The following script must be run in SQL Plus. Substitute ‘&1’ with username and ‘&2’ with password. This will create the database and provide proper permissions.

CREATE TABLESPACE &1 DATAFILE 'D:\app\oracle\oradata\arcsde\&1..DBF' SIZE 500M AUTOEXTEND ON;

create user &1 identified by &2 default tablespace &1;

alter user &1 quota unlimited on &1;

GRANT CREATE SESSION,
CREATE TABLE, 
CREATE TRIGGER,
CREATE SEQUENCE,
CREATE PROCEDURE,
CREATE INDEXTYPE,
CREATE LIBRARY,
CREATE OPERATOR,
CREATE PUBLIC SYNONYM,
CREATE TYPE,
CREATE VIEW,
DROP PUBLIC SYNONYM,
SELECT ANY TABLE,
ALTER ANY INDEX,
ALTER ANY TABLE,
ANALYZE ANY,
CREATE ANY INDEX,
CREATE ANY PROCEDURE,
CREATE ANY SEQUENCE,
CREATE ANY TRIGGER,
CREATE ANY VIEW,
DROP ANY INDEX,
DROP ANY PROCEDURE,
DROP ANY SEQUENCE,
DROP ANY TABLE,
DROP ANY VIEW,
EXECUTE ANY PROCEDURE,
SELECT ANY SEQUENCE,
ADMINISTER DATABASE TRIGGER
TO &1;

The following script must be run in DOS command prompt. Substitute ‘%1’ with username and ‘%2’ with password and ‘%3’ with ESRI EDN License File Name. This will setup ArcSDE and get it ready.

ECHO Setting Up  %1 with password %2
sdesetup -o install -d ORACLE11G -i 5151:%1 -u %1 -p %2 -l %3.ECP

In ArcSDE, when making connection (assuming 5151 port is running default ArcSDE Service)  make sure the connection is made to the correct schema not to the default SDE schema. Please check the snapshot below. In the sample "ECMV" is the Oracle Schema name which is also the username.

This was tested at ArcSDE 9.3 oracle 11g.

Good luck.

Cheers
Anand