Oracle user schema database depreciated - understanding the alternative

[robert_at_work]

Hi there,

I just learnt that creating user schema database in Oracle is no longer supported (>10.7).

I'm trying to understand how the new approach works - the description is from the documentation:

Multiple geodatabases in Oracle—Geodatabases in Oracle | Documentation 

To store multiple geodatabases when using an Oracle database management system, do one of the following:

• Install separate instances of Oracle and, in each instance, create a geodatabase. If you use Oracle Database 11g, this is the method to use.
• If you're using the multitenant architecture available starting with Oracle Database 12c, you can also use pluggable databases and create a geodatabase in each pluggable database.

In both cases, each geodatabase is maintained and upgraded independently. Each database can also be deleted independently. In each database, the geodatabase system tables are stored in the sde user's schema and user data is stored in the schemas of individual users.

Do I understand correctly that you still have an SDE schema and one user data schema? Its just that now you can only have one user-data schema anymore?

So lets assume I have up to now 5 user data schemas. Can I put all tables of the 5 user data schemas into one user-data schema oder do I have to create 5 pluggable database - one database for each user-data schema?

Rob

[George_Thompson]

You can have multiple data schemas in a single Oracle geodatabase. I have seen Oracle Enterprise Geodatabases with 10 - 20 user schemas.

The user-schema geodatabase concept is no longer / supported needed (as referenced in your other GeoNet post). You can have a single geodatabase with the SDE schema as the Geodatabase Administrator function, then multiple data owners (schemas) in the same Oracle DB instance. I cannot remember the main reasoning for user-schema EGDB's in the past for context.

Is your main goal to have all the users schemas (and SDE) in the same Oracle DB instance?

[robert_at_work]

Hi George,

I think I have difficulties with the terminology and get confused by it.

Here is what I understand:

Past & Present (up to Oracle 11g and ArcMap < 10.7)

In an Oracle database you have multiple schemas (aka "users"). ArcMap can connect to any Oracle schema and access spatial data if present (either as Oracle SDO or ESRI ST_Geometry).

With Arcmap you can create a "geodatabase" in Oracle. Either you use an existing Oracle schema or you can create an new schema. In the process ArcMap adds some tables to the schema and does other things in the background - and you get additional functionalty not available for "plain" Oracle schemas.
When you have such as geodatabase-enabled schema you also need an separate "SDE" schema. Within one Oracle database (instance) you can create multiple "geodatabase-enabled" schemas but you only have one SDE schema. Usually you have a single "geodatabase-schema" for a specific set of data (such as waste water network).

Is that called the "user schema geodatabase concept"?

Present & Future (>= Oracle 12g and ArcMap >= 10.7)

In Oracle you can have a "geodatabase-enabled" schema only in a pluggable database.
I also understand that you still have the SDE schema along the "geodatabase-enabled" schema holding your data.
Does that mean that - as I can have only one "geodatabase-enabled" schema in one pluggable database - I need multiple pluggable databases if I have - in the old world - multiple "geodatabase-enabled" schemas in one database (instance)? I dont understand how the new concept works - I only find the information that you cannot create "user schema geodatabases" anymore - but how does the new concept operates is not clear to me.

The online documentation says:

"Install separate instances of Oracle and, in each instance, create a geodatabase. If you use Oracle Database 11g, this is the method to use.

If you're using the multitenant architecture available starting with Oracle Database 12c, you can also use pluggable databases and create a geodatabase in each pluggable database."

https://desktop.arcgis.com/en/arcmap/10.7/manage-data/gdbs-in-oracle/multiple-geodatabases-oracle.ht...

I read "one geodatabase-enabled" schema per database (either per instance for 11g, or per pluggable database from 12)? Is my understand correct here? And you still would have one geodatabase-enabled schema for a specific set of data (such a waste water network) and not putting different data sets (waste water, gas, electricity) in one big geodatabase-schema?

Rob

[NanaDei]

Hi Rob,

I want to validate if you are using the user schema gdb model. "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."

Related link on user schema geodatabases.

How many rows are in your SDE.INSTANCES table?

Thanks

[robert_at_work]

Hi Nana,

sde.instances has 6 rows.

Rob