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 geodatabase administrator 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 for the specific database.

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.

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 might 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 might 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 might 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 geodatabase is owned by the user who created it. That user is the geodatabase administrator for that geodatabase and requires geodatabase administrator privileges. See User privileges for geodatabases in Oracle for specific geodatabase administrator privileges.
• A user can own only one geodatabase.
• Only the owner of the user-schema geodatabase can own data in that geodatabase.
• If a user owns a geodatabase, he or she cannot own data in the master sde geodatabase.
• If a user owns data in the master sde geodatabase, he or she cannot own a user-schema geodatabase.