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. Each geodatabase is maintained and upgraded independently. Each can also be uninstalled and deleted independently.
The second option uses one installation of Oracle. 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 install separate Oracle databases and create a geodatabase in each one, as shown in the following graphic:
Each geodatabase has an sde user schema in which the geodatabase system tables are stored. User data is stored in individual users' schemas.
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 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 as shown in the following graphic:
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. Be aware, though, that only the owner of the schema geodatabase can create datasets in it.
- 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 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.