Using multiple geodatabases within a DBMS

Each database management system (DBMS) has different rules for using multiple geodatabases. In general, though, it is possible to create separate databases, store a geodatabase in each one, and connect to each one using separate ArcSDE services no matter what DBMS you are using.

Beginning with 9.2, you can also create multiple geodatabases inside one Oracle database. This model for storing multiple geodatabases in one Oracle database is described below. This is followed by sections containing specific information for storing multiple geodatabases using other DBMS types and models.

Multiple geodatabases in one Oracle database

You can create multiple geodatabases in one Oracle database. When you do this, you create a geodatabase in a user's schema. These geodatabases contain their own ArcSDE and geodatabase system tables. There can be only one geodatabase per user schema. Geodatabases in the user's schema run concurrently with one master SDE geodatabase that is stored in the SDE schema.

The geodatabase in the SDE schema is always the master geodatabase and contains a table (SDE.INSTANCES) that keeps track of all the other geodatabases in the Oracle database. The SDE schema also contains the ST_Geometry type as well as its subtypes and functions.

Both the SDE geodatabase and user-schema geodatabases are created under a single Oracle database and can be accessed by a single ArcSDE service. 

 

Situations for which you may want to have multiple geodatabases in the same Oracle database include the following:

• If smaller groups within an organization, such as departments or project groups, will work independently of each other, they may want their own data. You could have a geodatabase for each group.
  
• Separating your development environment from your production environment
  
• Separately tuning each geodatabase for the specific applications it will service
  
• Protecting sensitive information
  For example, you may want to have a sensitive military database protected in its own geodatabase so owners of other instances will not be able to see the data.

Rules for using multiple geodatabases in one Oracle database

• A user can own only one geodatabase.
  
• Only one geodatabase can reference a dataset that has been registered with ArcSDE.
  
• A geodatabase is owned by the user who created it. That user is the ArcSDE administrator user for that geodatabase.

Creating geodatabases in a user's schema in an Oracle database

You create user geodatabases by running the sdesetup command with the install operation. For the instance (the –i option), you must specify the port number and the schema. For the user name and password, these must be the user name and password of the user who will own the geodatabase. The following is an example of this:

sdesetup –o install –d ORACLE10G –i 5151:Thor –u Thor –p rot –l license.txt

For the instance option, 5151 is the port number and Thor is the schema, which is also the user name since schemas correspond to user names in Oracle databases.

NOTE: Be sure you specify the schema. If it is not specified and only a port number is provided, the SDE schema will be used by default. Since the geodatabase in the SDE schema already exists, executing the sdesetup –o install command will upgrade the SDE geodatabase.

If you are using a direct connection, you still need to specify 5151:<user_schema> with the –i option. If doing a remote installation from a Windows machine, you also need to add a 5151 service in the Windows service file of the machine from which you are executing the sdesetup command.

It is recommended you store the system tables that get created with the sdesetup –o install command in a different tablespace than the one used for your SDE geodatabase. This will help avoid I/O contention and will allow you to backup the tablespace seperately if needed. To create the system tables of the user-schema geodatabase in a different tablespace, make a copy of your dbtune.sde file, and edit the parameters under the in the dbtune file to point them to a different tablespace. Do this prior to running the sdesetup command to create the geodatabase in a user's schema. For information on the dbtune file and how to alter it, see The dbtune file and the DBTUNE table.

NOTE: The user (in this example, Thor) must be given the same privileges you provide for the SDE user while installing or upgrading the SDE geodatabase.

Registering tables and layers
Users who own geodatabases are allowed to create tables in other geodatabases, but the table can be registered in only one geodatabase.

sdetable –o register –t tablename –i port:schemaname –u user –p passwd

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

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

For tables created with Oracle Spatial, you could use autoregistration for table and layer registration. Autoregistration registers as feature classes with the ArcSDE geodatabase any unregistered SDO_GEOMETRY tables. Autoregistration detects unregistered SDO_GEOMETRY tables by comparing the list of feature classes in the LAYERS table with the list of SDO_GEOMETRY tables in the USER_SDO_GEOM_METADATA table whenever a user connects and requests a list of feature classes from the ArcSDE server. To use autoregistration, the server configuration parameter DISABLEAUTOREG in the ArcSDE SERVER_CONFIG table must be set to FALSE.

Each ArcSDE geodatabase has its own LAYERS and SERVER_CONFIG tables. You must separately set to FALSE the DISABLEAUTOREG parameter in the user geodatabase's SERVER_CONFIG table to allow SDO_GEOMETRY tables to be autoregistered in the user's geodatabase.

Managing geodatabases created in user schemas

The user who owns the geodatabase is the ArcSDE administrator. In other words, the user is equivalent to the SDE user in the SDE master geodatabase. In the example geodatabase created above, the user Thor would perform such tasks as compressing or upgrading for the geodatabase Thor owns.

Note that since the geodatabase is in Thor's schema, the DEFAULT version for the geodatabase in the version table is named Thor.DEFAULT.

Starting and stopping a geodatabase in a user's schema

Geodatabases stored in schemas other than the SDE schema are dependent on the SDE geodatabase. Once you start or shut down the SDE geodatabase, all associated geodatabases will automatically be 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

ArcSDE Instance sdeora10g's instances on jupiter at Wed Mar 08 08:23:14 2006

Instance       	Created	               Id

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

 

NOTE: This will not work if you are only using direct connect because you cannot use the sdemon command if no ArcSDE service is running.

For details on the sdemon command, consult the ArcSDE Administration Command Reference installed 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

Creating connections to geodatabases in user's schemas

You can use an ArcSDE service or a direct connection to geodatabases owned by users other than SDE. However, only one giomgr process is used: the one to the SDE geodatabase. There are not separate giomgr processes for each geodatabase.

For ArcSDE service connections, use the port number and the schema as in the example 5151:Thor. (Note that the port number is the same for the SDE geodatabase and user-owned geodatabases.)

To create a direct connection to a user-owned geodatabase, specify the schema name in the connection using this:

sde:oracle10g:/:<schema_name>

If you don't provide a schema name, the SDE schema will be used by default.

NOTE: The forward slash, "/", between Oracle 10g and the schema_name is used in place of the database name, since Oracle does not use one.

Connecting to a geodatabase in a user's schema from ArcGIS

You can use ArcGIS 9.2 clients to connect to a user-owned geodatabase. You do this by specifying the port and schema (in the format port:schema) for the service name on the Spatial Database Connection Properties dialog box in ArcCatalog.

When you initially make a database connection in ArcCatalog, you are automatically connecting to the sde.DEFAULT version of the geodatabase. To connect to a geodatabase in the schema of a user other than SDE, click the Change button under Connection details on the Spatial Database Connection Properties dialog box and change the version name from sde.DEFAULT to <user_schema>.DEFAULT. In the following example, the schema name is TEST, so the connection would be to TEST.DEFAULT.

 

For more information on making a spatial database connection, see Creating spatial database connections.

Loading data into a geodatabase

You load data into geodatabases stored in a nonSDE user's schema the same way that you load data into the SDE master geodatabase—with either ArcCatalog (the recommended method) or ArcSDE commands.

It is important to note that if you are connected to two different geodatabases in the same Oracle database as the same user in ArcCatalog, when you try to copy and paste data between the geodatabases, the paste will fail.

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.

Creating a backup of a user-owned geodatabase

If all the tables in the geodatabase are owned by the geodatabase owner (in other words, if no other user created data in the geodatabase) and the geodatabase owner does not own data in any other geodatabase, you can create a backup of the geodatabase owner's schema. If users have data spread across multiple geodatabases in the same Oracle database, you would have to perform a complete Oracle system backup and restore.