User Schema geodatabase in Oracle 12.1.0.2

3486
8
03-04-2016 08:15 PM
CJ_Broome
New Contributor III

I have set up a ArcSDE v10.2.1 user schema (child) geodatabase (gdb)in Oracle 12.1.0.2 and because the user schema user is the only client who can add gis data to the database I am a bit confused on what to set the user's default tablespace to after the gdb has been created.  Right now it's set to the tablespace that houses the ArcSDE system tables for the child gdb.  I suspect that should not be the case because there is the chance that gis spatial data could be added to the ArcSDE system tables tablespace.   My question is this should the default tablespace for the user schema user be set to another tablespace that has been set up to store the gis business data for that user? and should the user schema ArcSDE system tables tablespace be granted unlimited quota?

0 Kudos
8 Replies
George_Thompson
Esri Frequent Contributor

Hi Callista,

Here some documentation for that configuration on Oracle. I have not seen to many people have user schema Oracle geodatabases any more. Many people just create a new data owner and map them to a separate table space.

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

When you ran the Create Enterprise Geodatabase GP tool, what was the table space name that was in the field?

-tablespace.jpg

That would be the location that all the objects would be stored unless you have something different in the DBTUNE table.

-George

--- George T.
0 Kudos
CJ_Broome
New Contributor III

Thanks for the reply George.  The geodatabase administrator name is testgdb and tablespace used in the Create Enterprise Geodatabase GP tool is named testgdb and is the default tablespace for user testgdb. Can you tell me if by modifying the dbtune DATA_DICTIONARY and DEFAULTS keyword parameters to point to the tablespaces other than the user schema user default tablespace, which stores the gdb system tables, no data objects will be created in the gdb system table tablespace.  The attached text file contains the what I think the dbtune DATA_DICTIONARY and DEFAULTS keyword parameters should be set to based on your response.  Let me know what you think?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

I strongly discourage creation of user-schema geodatabases because they don't provide the isolation that they purport to.

Case in point:  You should not create user-schema geodatabase tables in the SDE user's tablespace (doing so will likely overtop the SDE tablespace).  You should also not ever create user tables in the tablespace where either SDE or the user-schema geodatabase tables reside (the one client that convinced me it was necessary had three-plus tablespaces -- SDE, USERGDB, USERDATA1,...)

- V

0 Kudos
CJ_Broome
New Contributor III

Hi Vince,

We have been using user schema gdbs where the for years with no issues and wish to continue doing so.  The problem now is that the only user who can add data to the user schema gdb is the user schema user.  When that user is created their default tablespace is set to the tablespace that holds the gdb system tables for the user schema gdb not the master gdb.  I want to know if I should change the user schema user's default tablespace to point to a different tablespace so that no data objects are created in the user schema gdb system tables tablespace or should I just change all of the dbtune DEFAULTS keyword parameters to point to different tablespaces than the user schema user gdb system table tablespace.  Thoughts?

Thanks,

  Calli

0 Kudos
George_Thompson
Esri Frequent Contributor

Your comment below:

....The problem now is that the only user who can add data to the user schema gdb is the user schema user. When that user is created their default tablespace is set to the tablespace that holds the gdb system tables for the user schema gdb not the master gdb. .....

This is as designed, in the documentation it says:

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

  • A geodatabase is owned by the user who created it. That user is the geodatabase administrator for that geodatabase and requires geodatabase administrator privileges. See User privileges for geodatabases in Oracle for specific geodatabase administrator privileges.
  • A user can own only one geodatabase.
  • Only the owner of the user-schema geodatabase can own data in that geodatabase.
  • If a user owns a geodatabase, he or she cannot own data in the master sde geodatabase.
  • If a user owns data in the master sde geodatabase, he or she cannot own a user-schema geodatabase.

As Vince mentioned, this is not a very common or suggested workflow moving forward. If you are just creating a new Oracle geodatabase for your data, it is recommended to not use user-schemas.

--- George T.
0 Kudos
VinceAngelo
Esri Esteemed Contributor

As George writes, the restriction for owner in user-schema geodatabases is a design characteristic.

The default tablespace isn't a problem so much as a property that needs to be managed.  If you set the default tablespace for the user where you want USER data to go, then use the Tablespace Name option on the user-schema geodatabase creation tool, tables will go where they belong.

- V

0 Kudos
MarkChilcott
Occasional Contributor III

Hi Vince,

We haven't chatted for a while.  I noted you mention:

I strongly discourage creation of user-schema geodatabases because they don't provide the isolation that they purport to.

Our Delivery database is 99% simple read only feature classes.  There are one or two feature classes that are registered as versioned.  We are considering placing the versioned data into a user-schema geodatabase with the thought that it would make compress back to state zero easier, and thus make management of the instance more efficient.  Ie we would not have to kick all the users off the database to do a successful compress back to state zero – just the schema user.

 

Thoughts? ... Are we overthinking this?

Cheers,

Mark

VinceAngelo
Esri Esteemed Contributor

I'm not sure how versioning and state zero interact with user-schema geodatabases.  I don't see how this would make your implementation any easier -- If the only versioned tables are owned by the one user, and no other user accesses them, then why would they need to be logged out to complete a compress?  If you test this and it works for you, best of luck.  

- V

0 Kudos