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
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
- and yellow schema
- Is my understanding how a schema could be organized within the database correct?
- 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?
What structure would you recommend to make a later separation of the
FOOBAR-website and the
FOOBAR-services very easy
- different tablespaces for
- each schema within its own tablespace (or n spaces)?
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.