User-schema geodatabase management

Administration of geodatabases stored in a schema other than that of the sde user is 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 from those for the master geodatabase.

The user who owns the geodatabase is the geodatabase administrator. In other words, the user who 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.

TipTip:

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
TipTip:

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

To obtain the sdemon command, download the ArcSDE application server installation from the Esri Customer Care portal. The ArcSDE Administration Command Reference is included with this installation and explains how to use the sdemon command.

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

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 ArcGIS for Desktop tools.

However, only the schema owner can load data into this geodatabase. All other users must load data into the sde master geodatabase or into their own 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.

LegacyLegacy:

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.

You can load a table into a geodatabase with the same table name that exists in another geodatabase, since the tables will always be owned by different schemas.

Registering tables

You can create data using SQL or a third-party application, then 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, the SE_TABLE_REGISTERED_OUTSIDE_SCHEMA error will be returned. 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 Oracle database in ArcGIS for Desktop, open the Geodatabase Connection Properties dialog box, then change your connection to the user-schema geodatabase. See Connections to user schema geodatabases for more information.

Once you have connected to the user-schema geodatabase as the table owner, right-click the table under the database connection in the Catalog tree, point to Manage, then 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.

Creating a backup of a user-owned geodatabase

It is recommended that you do a complete Oracle system backup. If you are using the ST_Geometry storage type (which is the default storage type for geodatabases in Oracle 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.

6/12/2015