You can create multiple geodatabases in separate Oracle databases by setting up and installing each Oracle database as you would when setting up just one.

If you make a separate direct connection to each geodatabase, you do not have any additional configuration steps to make beyond the usual direct connection configuration. (See Setting up clients for a direct connection.) Then, when you make a connection to the database using database authentication, you need to specify the net service name appended to the end of the database password to indicate to which database you want to connect. For example, for a net service name of benedict2, you would type the password as follows:

mypassword@benedict2

If you make a direct connection to a remote Oracle database and use operating system (OS) authentication, you need to append the LOCAL variable and value to the direct connect syntax in the Service field in ArcCatalog or the server (–s) option at the command line. For instance, to use OS authentication for the net service benedict2, you would type the connection string followed by a forward slash, then LOCAL=benedict2, as shown below:

sde:oracl10g:/;LOCAL=benedict2

If you make a direct connection to a local Oracle database and use OS authentication, you append the ORACLE_SID variable and value to the direct connect syntax instead.

sde:oracle10g:/;ORACLE_SID=benedict2

If you use an ArcSDE service, each connection needs its own unique ArcSDE service name and port number. Therefore, you must add a new entry to the services file for the new service and port number. You must also create a copy of the dbinit.sde file for each additional geodatabase you create.

The following are the steps to create multiple ArcSDE service instances.

For Windows

1. Make a copy of your dbinit.sde file, giving it a new name. This name must include the name of the new ArcSDE service. For example, if the new service will be sdeora2, name the dbinit file dbinit_sdeora2.sde.
2. Update the new dbinit file with the proper DBMS connection variables (i.e., ORACLE_SID).
3. If you want to set up your second geodatabase with different parameters than your first geodatabase, also copy your dbtune.sde and giomgr.def files and give the copies different names.
4. If necessary, update the new giomgr.defs file to specify operating parameters of the new instance.
5. If necessary, update the new dbtune.sde file to specify parameters for creating data in the new geodatabase.
6. Run the ArcSDE for Oracle Post Installation wizard to create the geodatabase. Be sure to:
• Specify the net service name of the second Oracle database on the User information dialog box.
• Specify the .dbf file and path for the second database in the SDE user and tablespace information dialog box.
• Specify your customized dbinit.sde and giomgr.def files on the first ArcSDE configuration files dialog box.
• Choose the custom dbtune file option on the second ArcSDE configuration files dialog box and specify your customized file.
• Specify the net service name for the second Oracle database on the User information dialog box.
• Specify the service name, port number, password, and Oracle SID for the second Oracle database on the ArcSDE service information dialog box.

NOTE: If you do not run the Post Installation wizard but, instead, use sdesetup and sdeservice to create the geodatabase and service, you must manually update the services.sde and Windows services file with the new instance name, port number, and network protocol. The Windows services file is found at \WINNT\system32\drivers\etc\ or \WINDOWS\system32\drivers\etc\, depending on your operating system.

For UNIX/Linux

1. Make a copy of your dbinit.sde file, giving it a new name. This name must include the name of the new ArcSDE service. For example, if the new service will be sdeora2, name the dbinit file dbinit_sdeora2.sde.
2. Update the new dbinit file with the proper DBMS connection variables (i.e., ORACLE_SID).
3. If you want to set up your second geodatabase with different parameters than your first geodatabase, make backup copies of your existing dbtune.sde and giomgr.def files, giving them adifferent name.
4. Update the services.sde file with the new instance name, port number, and network protocol.
5. Add the service name, port number, and network protocol to the /etc/services file. Make sure the port number selected is not already in use.
6. If necessary, update the new giomgr.defs file to specify operating parameters of the new instance.
7. If necessary, update the new dbtune.sde file to specify parameters for creating data in the new instance's database.
8. Run the sdesetup command to create the geodatabase in the second Oracle database and authenticate the software.

sdesetup -o install -d <ORACLE9I | ORACLE10G | ORACLE11G> -s <datasource> -l <license_key> -p <DB_admin_password>



9. Start the new ArcSDE service using the sdemon command.

sdemon -o start -i <service_name> -p <ArcSDE_admin_password>

You can create multiple geodatabases in one Oracle database. When you do this, you create a geodatabase in the schema of a user other than SDE. 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 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, its subtypes and functions, and the system tables it uses, such as ST_SPATIAL_REFERENCES.

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, 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 services
• Protecting sensitive information
For example, you may want to have a sensitive military database protected in its own geodatabase so owners of other instances are not able to see the data.

Rules for using multiple geodatabases in one Oracle database are as follows:

• 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

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. See User permissions for geodatabases in Oracle for details.

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 is used by default. Since the geodatabase in the SDE schema already exists, executing the sdesetup –o install command will upgrade the SDE geodatabase.

Be aware that even if you are using a direct connection to the master SDE geodatabase, you must provide a valid service number with the –i option. If no service was created for the master SDE geodatabase, one must be created (though it doesn't have to be started) on Windows. This service must be added to the Windows services file. On UNIX or Linux, the service must be added to the services.sde file and the /etc/service file.

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 separately 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 DATA_DICTIONARY keyword 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: If the tablespace for the user-schema geodatabase is the default schema of the user, and if you had left the tablespace values for the DATA_DICTIONARY parameters commented out (in other words, you had not specified tablespaces for the SDE master geodatabase), you do not have to alter the parameters of the DATA_DICTIONARY keyword to point to the new schema.

Registering tables and layers

Users who own geodatabases are allowed to create tables in other geodatabases, but a 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 that owns the schema in which the geodatabase is stores is equivalent to the SDE user in the SDE master geodatabase. In the example geodatabase created in the previous section, 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 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

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. If your site does not use an ArcSDE service, you can use SQL to query the INSTANCES system table to obtain information about user-schema geodatabases.

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

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

sde:oracle10g:/:<schema_name>

If you don't provide a schema name, the SDE schema is 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 from ArcGIS Desktop to a geodatabase in a user's schema

You can use ArcGIS 9.2 or later 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.

The preceding example shows a connection using an ArcSDE service using port number 5152. To make a direct connection to the test user-schema geodatabase, the syntax would be as follows:

sde:oracle10g:/:test

For more information on making a spatial database connection, see Creating spatial database connections in the "Data management with ArcCatalog" section of the help. For connection syntax, see ArcSDE connection syntax.

Loading data into a user-schema geodatabase

You load data into geodatabases stored in the schema of a user other than SDE 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 datasets between the geodatabases, the paste will fail. 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.

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.

Deleting a geodatabase from a user's schema

Beginning with ArcGIS 9.3, you can delete the ArcSDE geodatabase repository from a user's schema by doing the following:

1. Unregister or remove from the user's schema all data that is registered with ArcSDE and/or the geodatabase. This includes such objects as feature datasets, stand-alone feature classes, raster datasets, raster catalogs, nonspatial tables—any object that contains a record in the TABLE_REGISTRY ArcSDE system table. This also applies to objects registered in another user's schema. For example, there could be a feature class in the user2 schema that is registered in the master SDE geodatabase. This, too, would need to be removed or unregistered before you could delete the user-schema geodatabase.
2. Make a backup of the user's schema.
3. Be sure there are no users connected to the SDE master geodatabase or any of the user-schema geodatabases.
4. Execute the sdesetup command with the delete operation to remove the ArcSDE repository (the system tables, views, indexes, triggers, operators, packages, package bodies, libraries, type bodies, indextypes, and types) from the user's schema. Consult the ArcSDE Administration Command Reference for details on the sdesetup command.