User-schema geodatabase management

For the most part, geodatabase management is the same for user-schema geodatabases and the sde geodatabase. The main difference is in who performs the management tasks; in a user-schema geodatabase, the schema owner is the geodatabase administrator rather than the sde user.

The few administration tasks that do differ are listed in the following sections.

Prevent connections to a user-schema geodatabase

User-schema geodatabases are dependent on the sde geodatabase. When you prevent connections to the sde geodatabase, connections to all associated geodatabases are also blocked.

Create stored procedures for geodatabases stored in a user's schema

Each geodatabase has its own set of stored procedure packages that are automatically created in the geodatabase administrator's schema upon geodatabase creation.

In addition, you can create individual packages in the geodatabase using an SQL client if you provide the schema name. For example, to create the dbtune_util package in the Thor user schema, issue the following command at the SQL prompt:

@dbtune_util.sps THOR

Load data into a user-schema geodatabase

You load data into user-schema geodatabases the same way that you load data into the sde geodatabase—with ArcGIS Desktop tools.

However, only the schema owner can load data into a user-schema geodatabase. All other users must load data into the sde geodatabase or into their own user-schema geodatabases. For example, if user1 owns a schema geodatabase, user1 can only create data in the user1 geodatabase. If user2 does not own a schema geodatabase, user2 can only create data in the sde geodatabase. If a user owns data in more than one geodatabase in the same Oracle database, move the data into one sde geodatabase.


Existing user-schema geodatabases (those created with ArcGIS 10 or earlier) that contain data owned by other users can be upgraded, and those data owners can continue to create new data and use their existing data. For example, if user1 created a geodatabase in ArcGIS 9.2, and user2 created data in that geodatabase with an ArcGIS 10 or earlier client, then user1's geodatabase was upgraded to 10.1, user2's existing data is still accessible, and he can continue to create data in user1's geodatabase. However, it is recommended that you move data to the sde geodatabase if possible.

Be aware that the amount of time it takes to connect to the Oracle database will increase as the number of user-schema geodatabases you create and use increases. This is especially true if the user-schema geodatabases contain a mix of geodatabase tables and tables that are not registered with the geodatabase.

Register tables

You can create data using SQL or a third-party application and register that table with a geodatabase. However, a table can be registered in only one geodatabase. If the table has already been registered in another schema, ArcGIS returns the SE_TABLE_REGISTERED_OUTSIDE_SCHEMA error. Also, as mentioned previously, users can only register data in one geodatabase within an Oracle database.

Create a backup of a user-schema geodatabase

You should create a complete Oracle system backup rather than a backup of just the user's schema. That is because the geodatabase in the user's schema relies on information in other schemas in the database; therefore, creating a backup of only the schema that contains the geodatabase is insufficient. See your Oracle documentation for information on creating database backups.