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