Multiple geodatabases in Oracle

There are two possible ways to store multiple geodatabases when using an Oracle database management system (DBMS): you can install separate instances of Oracle and, in each instance, create a geodatabase, or you can create a master geodatabase in an Oracle instance and also create dependent geodatabases in other users' schemas in that same instance.

The first option requires you to install multiple instances of Oracle. If you use an ArcSDE service to connect to the geodatabase, you need one service for each geodatabase. Each geodatabase is maintained and upgraded independently. Each can also be uninstalled and deleted independently.

The second option uses one installation of Oracle, one installation of ArcSDE, and one ArcSDE service to connect. It requires that you have multiple users in the database, each of whom has been granted ArcSDE administrative privileges to install, administer, and upgrade the geodatabase stored in his/her schema. Each geodatabase is maintained and upgraded independently. You can delete individual geodatabases in a user's schema after removing all registered data, but you cannot delete the master geodatabase without deleting all the geodatabases stored in users' schemas.

Information about each option is given in the following sections.

Multiple geodatabases in separate Oracle databases

You can create multiple geodatabases in separate Oracle databases by setting up and installing each Oracle database as you would when setting up just one.

If you make a separate direct connection to each geodatabase, you do not have any additional configuration steps to make beyond the usual direct connection configuration. Then, when you make a connection to the database using database authentication, you need to specify the net service name appended to the end of the database password to indicate to which database you want to connect. For example, for a net service name of benedict2, you would type the password as follows:

mypassword@benedict2

If you make a direct connection to a remote Oracle database and use operating system (OS) authentication, you need to append the LOCAL variable and value to the direct connect syntax in the Service field in ArcCatalog or the server (–s) option at the command line. For instance, to use OS authentication for the net service benedict2, you would type the connection string followed by a forward slash, then LOCAL=benedict2, as shown here:

sde:oracl10g:/;LOCAL=benedict2

If you make a direct connection to a local Oracle database and use OS authentication, you append the ORACLE_SID variable and value to the direct connect syntax instead.

sde:oracle10g:/;ORACLE_SID=benedict2

See Making a direct connection from ArcGIS Desktop to a geodatabase in Oracle for complete connection instructions from the Desktop application.

If you use an ArcSDE service, each connection needs its own unique ArcSDE service name and port number. Therefore, you must add a new entry to the services file for the new service and port number. You must also create a copy of the dbinit.sde file for each additional geodatabase you create.

See Using multiple ArcSDE services on one Windows server or Using multiple ArcSDE services on one Linux or UNIX server for instructions to create multiple ArcSDE service instances.

Multiple geodatabases in one Oracle database

You can create multiple geodatabases in one Oracle database. When you do this, you create a geodatabase in the schema of a user other than that of the sde user. For this reason, these geodatabases are referred to as user-schema geodatabases. These geodatabases contain their own ArcSDE and geodatabase system tables.

There can be only one geodatabase per user schema. Geodatabases in the user's schema run concurrently with one master geodatabase that is stored in the sde user's schema. Because the master geodatabase is stored in the sde user's schema, it is referred to as the master sde geodatabase.

The geodatabase in the sde schema is always the master geodatabase and contains a table (SDE.INSTANCES) that keeps track of all the other geodatabases in the Oracle database. The sde schema also contains the ST_Geometry type, its subtypes and functions, and the system tables it uses, such as ST_SPATIAL_REFERENCES.

Both the sde master geodatabase and user-schema geodatabases are created under a single Oracle database and can be accessed by a single ArcSDE service.

Situations for which you may want to have multiple geodatabases in the same Oracle database include the following:

• If smaller groups within an organization, such as departments or project groups, work independently of each other, they may want their own data. You could have a geodatabase for each group.
• If you need to separate your development environment from your production environment you could use a geodatabase in a user's schema for the development environment for testing.
• Create separate user-schema geodatabases so you can tune each geodatabase for the specific applications it services.
• You can protect sensitive information by putting sensitive data in a separate user-schema geodatabase to which only specific users have access.

  For example, you may want to have a sensitive military database protected in its own geodatabase so owners of other instances are not able to see the data.

Rules for using multiple geodatabases in one Oracle database are as follows:

• A user can own only one geodatabase.
• Only one geodatabase can reference a dataset that has been registered with ArcSDE.
• A geodatabase is owned by the user who created it. That user is the ArcSDE administrator user for that geodatabase.