Problem with Multiple GDBs in Oracle

[AnnalieseVollick]

Oracle 11gR2/ArcSDE 10.1/Unix AIX 7.1

Ahead of a call to tech support I would like to post in case there is experience that may help.

I have a Mulitple Geodatabase in one Oracle schema that goes something like this: SDE (master), SDE_1 (user), SDE_2 (user).  Within these I use data creators to import data. SDE/MAIN, SDE_1/DATA_1 and SDE_2/DATA_2.

*I can create a connection to SDE logged in as MAIN and import data.
*I can create a connection to SDE_1 logged in as DATA_1 (change geodatabase connection to SDE_1 default) and import data.
*I could create a connection to SDE_2 logged in as DATA_2 (change geodatabase connection to SDE_2) and import data. Now, when I connect and log in the same way I receive the error message: "The connected user cannot create data in a schema geodatabase for which they are not owner. Connect as the schema geodatabase owner."  I can import data into SDE_2 logged in as SDE_2 - but not as the creator (DATA_2). I can create a table in SQLPlus logged in as DATA_2.  I am not sure where to look to fix.  I've been looking through permissions, connection, etc which all seem to match the other user schema gdb (SDE_1/DATA_1) and find it confusing that it used to work, now doesn't and still works in the other two instances.

Any ideas would be appreciated.
Annaliese

[VinceAngelo]

I've seen this use paradigm a lot lately (with multiple "SDE" users), but I don't think it was intended.
My understanding is that the DATA_1 and DATA_2 users were intended to be both the registered
schema *and* the owners of all the data. 

I've never found a benefit to user-schema databases, so I haven't used them much, but I have
a contract to help a client improve user-schema geodatabase performance, so maybe my
testing will find some redeeming quality.

- V

[BirajaNayak]

Hi Annaliese,

From 10.1, the design of multiuser schema model is changed. In user schema, only schema owner can own data not other user.
For your case, only sde_1 can import data to sde_1 and sde_2 can import data to sde_2. But in sde schema main, data_1, data_2 can import data.

Please check the below web help for more information:

http://resources.arcgis.com/en/help/main/10.1/index.html#/Multiple_geodatabases_in_Oracle/002n000000...

In heading "Multiple geodatabases in one Oracle database"

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

1. A user can own only one geodatabase.
2. A geodatabase is owned by the user who created it. That user is the geodatabase administrator user for that geodatabase and requires the same database privileges.
3. The owner of the geodatabase can only own data in that geodatabase; he or she cannot own data in another user's schema or the master sde geodatabase.
4. Users can only own data in one geodatabase. For example, user1 can own data in the master sde geodatabase or user2's geodatabase, but not both.

Hope this clarifies your doubt.

Regards,
Biraja

[AnnalieseVollick]

Thanks for the replies.

I think this is what I've run into:
Existing user-schema geodatabases (those created with ArcGIS 10 or earlier) that contain data owned by users other than the schema owner can be upgraded, and those data owners can continue to create new data and use their existing data. For example, if user porthos created a geodatabase in ArcGIS 9.3, user aramis created data in that geodatabase with an ArcGIS 10 or 9.3 client, then porthos' geodatabase was upgraded to 10.1, aramis' existing data is still accessible and he can continue to create data in porthos' geodatabase. However, it is recommended that you move data either to the sde master geodatabase or the user's own schema geodatabase if possible.

The SDE_2/DATA_2 I had to delete (with ArcCatalog) data in it to re-import.  Now that it is empty, I guess it recognizes (???) that  relationship now and hence the error. Whereas the SDE_1/DATA_1 relationship still persists.  Both were upgraded from 10.0 to 10.1.

Annaliese

[BirajaNayak]

Yes. you got it.