User-schema geodatabase management

Administration of geodatabases stored in a schema other than that of the sde user are managed somewhat differently than the sde master geodatabase on which they are dependent. This topic includes a few of the management tasks that have different procedures than those for the master geodatabase.

The user who owns the geodatabase is the ArcSDE administrator. In other words, the user that owns the schema in which the geodatabase is stored is equivalent to the sde user in the sde master geodatabase and is the user who performs administrative tasks in the user-schema geodatabase.

Tip:

Other user-schema geodatabase information can be found in Connections to user-schema geodatabases and Deleting a geodatabase from a user's schema.

Starting and stopping a geodatabase in a user's schema

Geodatabases stored in schemas other than the sde schema are dependent on the master geodatabase. Once you start or shut down the master geodatabase, all associated geodatabases are automatically started or shut down. An attempt to start a geodatabase stored in a user's schema independently will result in an error similar to the following:

init_DB DB_instance_open_as_dba: -93
DBMS error code: 1017
ORA-01017: invalid username/password; logon denied

Listing the geodatabases present on a server

You can find out what geodatabases are running on a server by using the –I option on the sdemon –o info command. This option will return information regarding the various geodatabases that are present on a server.

sdemon –o info –I instances

Instance       	Created	               Id

SDE        Thu Oct 28 16:30:20 2004        0
MAP	  Mon Feb 27 11:18:04 2006        1

Tip:

You can also use SQL to query the INSTANCES system table to obtain information about user-schema geodatabases.

For details on the sdemon command, see the ArcSDE Administration Command Reference provided with ArcSDE.

Creating 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 ArcSDE 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

Loading data into a user-schema geodatabase

You load data into geodatabases that are stored in the schema of a user other than the sde user the same way that you load data into the sde master geodatabase—with either ArcGIS Desktop (the recommended method) or ArcSDE commands.

Caution:

It is important to note that if you are connected to two different geodatabases in the same Oracle database as the same user in ArcGIS Desktop, when you try to copy and paste datasets between the geodatabases, the paste fails. This is due to the fact that the dataset does not have a unique fully qualified name since it is stored in the same user's schema.

You can load a table into a geodatabase with the same table name that exists in another geodatabase as long as the tables are owned by different schemas.

Registering tables and layers

Users who own geodatabases are allowed to create tables in other geodatabases using SQL or ArcSDE commands, but a table can be registered in only one geodatabase.

If the table has already been registered in another schema, the SE_TABLE_REGISTERED_OUTSIDE_SCHEMA error will be returned.

The following example shows registering with ArcSDE a table, mytable, that was created using SQL:

sdetable –o register –t mytable –i 2299:schemaname –u user –p passwd

This example shows creating a layer, mylayer, using the sdelayer command.

sdelayer –o create –l mylayer,shape –i port:schemaname –u user -p passwd

See the ArcSDE Administration Command Reference, which is included on the ArcGIS Server Enterprise installation media, for details on using the sdetable and sdelayer commands.

Creating a backup of a user-owned geodatabase

If all the tables in the schema geodatabase are owned by the schema owner (in other words, if no other user created data in the geodatabase), the geodatabase owner does not own data in any other geodatabase, and you are not using the ST_Geometry storage type, you can create a backup of the geodatabase owner's schema.

In most cases, it is recommended that you do a complete Oracle system backup. If users have data spread across multiple geodatabases in the same Oracle database, or you are using the ST_Geometry storage type (which is the default storage type for ArcSDE geodatabases beginning with ArcGIS 9.3), a complete Oracle system backup is required. 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.