User-schema geodatabase management

For the most part, geodatabase management is the same for user-schema geodatabases and the master 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 master sde geodatabase. When you prevent connections to the master 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 through SQL*Plus 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 master 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 master 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 master geodatabase. If a user owns data in more than one geodatabase in the same Oracle database, the geodatabases cannot be upgraded, and the user-schema geodatabase cannot be deleted. You must delete the data from the other geodatabases before you can upgrade.

Legacy:

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 either to the sde master geodatabase or the user's own schema geodatabase if possible.

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.

To register a table with a user-schema geodatabase, connect to the user-schema geodatabase as the table owner, right-click the table under the database connection in the Catalog tree, point to Manage, and click Register with Geodatabase.

ArcGIS detects whether a qualifying column exists to be used for an ObjectID (a not null, integer column) and what the entity type, spatial reference, and spatial extent are if you are registering a spatial table. If ArcGIS cannot detect this information, you will be prompted to provide it. See Registering a table with the geodatabase for more information.

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.