NIS geodatabase configuration in Oracle (Maritime Charting)

It is recommended that the NIS be implemented as separate user-schema geodatabase in a single Oracle instance. A master geodatabase is created under the sde schema, which keeps track of the NIS geodatabase and contains the ST_Geometry type, its subtypes and functions, and the ST_SPATIAL_REFERENCES system table. The NIS geodatabase contains its own ArcSDE and geodatabase system tables and will contain all of the GIS feature classes and tables that support the ArcGIS for Maritime:Charting.

If additional schemas are needed to implement other Esri products such as ArcGIS Data Reviewer for Desktop or ArcGIS Workflow Manager for Desktop, it is recommended that these also be created as separate user-schema geodatabases.

Tablespace configuration

It is recommended that you store the system tables for the NIS user-schema geodatabase in separate tablespaces than the ones used for the sde master geodatabase.

The tablespaces, which can be created manually, will be used when the Create Enterprise Geodatabase geoprocessing tool in ArcGIS for Desktop is run. If the tablespace is not set up manually, it will be created automatically by the system during the geodatabase creation process with default sizing and parameters. Creating the tablespace manually allows you to better control the size and parameters.

The following example can be used to create tablespaces in Oracle as the sys user in SQL*Plus. Replace <text> with values appropriate for your organization.

Create NIS tablespace – user-schema geodatabase repository and NIS GIS data

Example:

create tablespace nis
       datafile '<data location>/<SID>/nis01.dbf' size 2G autoextend off,
                '<data location>/<SID>/nis02.dbf' size 2G autoextend off,
                '<data location>/<SID>/nis03.dbf' size 2G autoextend off
       logging
       extent management local uniform size 1M
       segment space management auto;

Database users and roles

Specific privileges must be granted to users based on functions they need to perform on the Oracle database. Roles will be created to manage these groups of privileges and users will be created and assigned to the appropriate roles based on their function.

Learn more about user privileges for geodatabases in Oracle

Roles

There are some predefined roles that will be created in Oracle which will be assigned to different users for carrying out different tasks. They can be created using the examples provided in this section by executing the scripts as sys user in SQL*Plus.

Create SCHEMA_OWNER role – assigned to data owners/creators

Example:

create role SCHEMA_OWNER;

grant CREATE SESSION              to SCHEMA_OWNER;
grant CREATE SEQUENCE             to SCHEMA_OWNER;
grant CREATE TRIGGER              to SCHEMA_OWNER;
grant CREATE VIEW                 to SCHEMA_OWNER;
grant CREATE TABLE                to SCHEMA_OWNER;

Create CDR_EDITOR role – assigned to NIS (central database repository) users

Example:

create role CDR_EDITOR;

grant CREATE SESSION              to CDR_EDITOR;

Create CDR_VIEWER role – assigned to NIS viewers for read-only access

Example:

create role CDR_VIEWER;

grant CREATE SESSION              to CDR_VIEWER;

Users

Users will be created in Oracle, which will be assigned the appropriate roles from the previous section based on their function. They can be created using the examples provided in this section by executing the scripts as sys user in SQL*Plus.

Create NIS user

Example:

create user nis identified by <password> default tablespace nis;
alter user nis quota unlimited on nis;

/* The SDE_UPGRADE role needs to be granted only during  */
/*   geodatabase creation and upgrade.  Afterward, this  */
/*   role may be revoked and the SDE_ADMIN role granted  */
grant CONNECT, SCHEMA_OWNER, SDE_UPGRADE to nis;

Create NISEDITOR user

Example:

create user niseditor identified by <password> default tablespace nis;
grant CONNECT, CDR_EDITOR to niseditor;

/* Grant these privileges for shared log file creation     */
/*   They may be revoked from this role once the log files */
/*   have been created by selecting more than 100 features */
/*   in an ArcMap session                                  */
alter user niseditor quota unlimited on nis;
grant CREATE SEQUENCE             to NISEDITOR;
grant CREATE TABLE                to NISEDITOR;

Configuring the Oracle database to support ArcSDE XML columns

The NIS data model includes ArcSDE XML columns as of version 10.1. ArcSDE XML columns use the Oracle Text components, which must be included when configuring a new Oracle database instance. To use them, you must grant privileges to users who will own the XML columns. Execute the following statement as the sys user in SQL*Plus.

grant EXECUTE ON ctxsys.ctx_ddl to NIS;

If XML columns are written in languages other than English, additional Oracle and ArcSDE configuration may be necessary.

Learn more about configuring an Oracle database to support ArcSDE XML columns