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.
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.
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.