Understanding of schema and tablespaces - recommendation for a later seperation [closed]

Question

DLDR: I would like to get

• a confirmation of my understanding of tablespaces and schemas (see image)
• a recommendation how to organize schemas and tablespace to make a later separation easy.

Background: In our project we have two different teams that are working on the implementation of a website. The idea is to later separate the basic-services from the website (cms / portal) so that different websites / portals can be created an all are using the same independent basic-services.

The current installation looks like this:

TABLESPACE_N  MB  AUTOEXTENSIBLE
FOOBAR_DATA    1024       YES 
FOOBAR_INDEX   1024       YES 
FOOBAR_LOB     1024       YES 
SYSAUX         600       YES 
SYSTEM         700       YES 
TEMP          1024       NO
UNDOTBS1       456       YES 

This question is about how to move a schema to another tablespace. Some recommendations store indexes in another filesystem are controversial / questionable. But this is not the scope of my question. I would to get a recommendation how to organize schema and tablespace to make a later separation easy.

Confirmation / Clarification: My underständing of oracle is the following:

• Tablespace: logical storage unit, one or more datafiles
• Datafiles: physical structure that conforms to the OS, a datafile can be associated with only one tablespace and only one database
• Schema: a collection of logical structures of data (table, index), or schema objects (view,..), same name as the db-user that owns it, each user owns a single schema. [A USER may be given access to SCHEMA OBJECTS owned by different USERS.]

In the attached image you can see two schemas:

• white schema S1
• and yellow schema S2

Question 1:

1. Is my understanding how a schema could be organized within the database correct?
2. So we could create two schemas (/ two users) and separate the objects for the FOOBAR-services (yellow) from the FOOBAR-website (white) in any way we want to?
   • Schema 1 could only use tablespace 1 with datafile 1 and 2?
   • Schema 2 could only use tablespace 2 with datafile 3?

Question 2:

What structure would you recommend to make a later separation of the FOOBAR-website and the FOOBAR-services very easy

• different tablespaces for FOOBAR-Web and FOOBAR-Services?
• each schema within its own tablespace (or n spaces)?

Answer 1

I think you are over complicating this. Grants and roles govern access by users not what table space and data files where the data is located in. Yes, backup, recover and export are much easier if schemas have their own table space but that doesn't seem to be your question.

• Separate your business domains by user/schema.
• Give each user/schema their own group of tablespaces which contains their tables/indexes/lobs eg: User1Data, User1Indexes, User1LOBS
• create roles to allow users/schemas to access data/code in other schemas

From your comment below I would add:

• user/schema are the logical way to separate data and code. Use different schemas from day one if your business domain works that way (and that seems to be what you are indicating). Moving tables to different schemas after deployment can be done but it's a pain.
• in your comment you still link table spaces and permissions. There is no link between what table space a user's data is in and the permissions granted on that data.

If you will allow me a simplification:

• users and schemas allow you to control access to data and code through roles and grants
• table spaces and data files allow you to manage the physical files used by the database. Easier backup and recovery are one of the things that is easier to do when each user has their data in a clearly defined set of table spaces.