Re: schemas & tablespaces

Hmm - this is a big topic. A couple of pointers that might help

1. Unlearn SQLServer. You will need to understand how and why Oracle does things, there are many things Oracle does that have no equivalents in SQLServer
2. Think of schemas as logical objects created in a namespace. Your mapping of an application to a schema is valid.
3. Think of tablespaces as the physical structure of your database. Typically you design your tablespaces to meet requirements such as data to disk placement, as a way of grouping physically similar types of data together, as a way of meeting online backup requirements etc.

So for instance, an Order Entry application may have a logical schema called OE that contains all the logical database objects required by the application. Users of the Order Entry application can be given the necessary privileges required to access the logical objects in the OE schema. You could use Export/Import to move the OE schema (and associated data, if required) from one database to another.

In turn, the database may have a number of tablespaces, say the OE_DATA tablespace for the actual data stored in the tables defined in the OE schema, the OE_INDEX tablespace for the actual data stored in the indexes defined in the OE schema, the OE_TEMP tablespace for the temporary data segments the users of the Order Entry application require for sorts etc. Another application (and schema could have a totally different set of tablespaces). You could then easily backup just the Order Entry data by backing up the relevant tablespaces (plus the other typical files required, of course). Alternatvely, you could just have one big tablespace for all data (not recommended), or have one tablespace for each schema (also not recommended). You get to decide how you want to structure the data storage to meet your requirements.

To summarize - schema for logical definition, tablespaces for physical definition. The advantage is that you have multiple levels of control.

> From: alvie_at_my-deja.com
> Organization: Deja.com
> Newsgroups: comp.databases.oracle.server
> Date: Wed, 17 Jan 2001 03:57:28 GMT
> Subject: schemas & tablespaces
>
> Hi group
> I'm coming from a MS SQL Server background and want to know what the
> best method for creating new schemas is, as far as tablespaces are
> concerned. Is it better to create a new tablespace for each new
> schema? I'm equating schemas with SQL Server databases. Each schema
> will be application specific. If not, what is the recommended approach?
>
> thanks
> -as
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Jan 16 2001 - 23:23:33 CST