nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

George Thompson

Using the configuration keyword is for data loaded after the change. It does NOT take currently existing data and update it, as I understand it.

I am not sure why you would want the "data" and other associated items in a different table space, especially leaving them in SDE.

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 @KimberlyGarbade

Thanks

ModyBuchbinder

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

@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 ?

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

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

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

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

KimberlyGarbade

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.).

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.