Separating SDE system tables from user tables

[yockee]

Hi,

somehow i got confused..

How do i separate user tables from sde system tables / objects ? 

Do I :

- create a new schema with a user and table space ?

or...

- create a new user with tablespace ? 

any suggestions or article that i can read ?

Thanks

iam using Oracle 12c, ArcGIS Pro 2.8

[KimGarbade]

When you use the Create Enterprise Geodatabase tool it will give you the option to either have a user named SDE own the SDE schema, or have dbo own the SDE schema.  

 A note here is that you should set up a user other than SDE to actually own data (I.E. Feature Classes, tables, etc.).

[yockee]

Thanks for your time... I am using Oracle. Maybe same principle can be applied here. thanks

[George_Thompson]

I would recommend loading the data with a separate user (not SDE) and have the objects in a different tablespace. There are MANY different ways that this is done within Oracle.

Here is some help doc, that may help: https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/user-accounts-groups.htm

[yockee]

Hi George,

Thanks

So, its not necessary to create another schema to separate the user data from system tables ? Creating new user and assign it to default tablespace is sufficient enough. Is it ?  

[yockee]

I meant, what is the choice for easy maintenance (backup, tuning, security..etc), put user data into different schema (automatically create new user to own the schema and new tablespace) OR just create user with default tablespace ?

Thanks

[George_Thompson]

This is really up to the database admin and geodatabase admin in how it is architected.

You can put all the objects in the same tablespace, if you want. This may be "easiest" but there could be other items that would need them to be separated. I am not sure there is "easy maintenance" without a detailed review of needs, requirements, etc.

As far as the objects, the ArcGIS clients know and display them as separate when you look in ArcGIS Pro, etc.

[yockee]

@George_Thompson and all,

Is it better to separate tablespace for SDE system tables and user tables onto different disk (physically) or the same disk ?

[mody_buchbinder]

When you create enterprise geodatabase in Oracle it create the SDE schema and put all system tables in it.

If you have a small database with single login you might keep all data in this schema.

In most cases you would like to use different users and permissions, then the SDE tablespace will only include the system tables and other "real" data will be saved to other tablespaces.

A backup will need to include the SDE schema and the real data schema.

Where to keep each tablespace is a question for DBA administrator.

Do you keep the manage oracle tablespace on a different disk then other schemas?

The SDE tablespace should be small, only the system tables with not too many records.

Have Fun

[yockee]

Hi,

I want to move ALL user data into different tablespace, separating them from SDE System files. I just want to move the base table, without moving the indexes etc. 

is doing it via ArcSDE Configuration keyword the same as issuing this particular command :

ALTER TABLE SDE.ROAD_DATA MOVE TABLESPACE  DATAUSER1;

? 

I have done a little research, by issuing that ALTER TABLESPACE command, all the functionality are still normal, such as : editing from arcgis pro, updating attribute via feature service and editing the shape via feature service, also all the viewing (pan, zoom), select from Arcgis pro are still working normally. Are these indicators sufficient to qualify what I am doing is correct ?

many thanks

@mody_buchbinder @George_Thompson @KimGarbade 

Thanks