Re: schemas & tablespaces

In article <943575$12s$1_at_nnrp1.deja.com>,   alvie_at_my-deja.com wrote:
> 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/
>

Schema equate to database users in MSSQLServer. A schema is a logical grouping of database objects. Often a schema also includes a user environment; including defaults, security, etc. In this regard, your use of schema to distinguish application context is appropriate.

Although I have found no good analogy of a MSSQLSvr database in Oracle, I have found it useful to think of tablespaces as analagous to implicit MSSQLSvr databases.

Truly, a tablespace's primary role is the directed allocation of physical disk space to data storage. Oracle uses tablespaces much the same way that an operating system uses disks. In fact, there is often a correlation between the number of tablespaces and the number of disks available.

Oracle recommends following these basic guidelines (among others) when allocating tablespaces...

Before 1) Never use the system tablespace to house user objects.

1. Place tables and indexes in seperate tablespaces. Watch for indexes created by primary keys because they default to the default tablespace for the user.
2. Within a single tablespace, objects should have regular/similar extent sizes. This helps to reduce fragmentation. I usually try to preallocate extents of exactly the same size within a tablespace.
3. Keep the number of extents small within a segment. Although this does not pertain directly to tablespace allocation, it does serve as a contributing factor when considering #2.
4. Use separate tablespaces for rollback segments, temporary segments and 3rd-party application data.

With this said, I usually create a minimum of four >user< tablespaces: DATA, INDEX, BIG_DATA, BIG_INDEX. The names speak for themselves.

I then estimate the size of each data segment and explicitly declare initial and next extent sizes (next = initial = common tablespace extent size) within the appropriate tablespace. This allows me to tightly control the fragmentation due to automatic growth.

If the needs of your application(s) are greatly varied, you may need more tablespaces to manage multiple segment sizes.

It is also worth reviewing segment activity to determine high usage segments. These may warrant special attention. Again, this follows similar methods to the allocation of disk resources.

Hope this helps...

-Brian

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 16 2001 - 23:34:39 CST