Re: Advantage of Tablespace

1. What are admn. operations on all application's objects. eg : backup. What other admn. operations could be there ?

   ---

   Database objects(segments) should be grouped according to its type. The behaviour of each segment differs. There is a helpful standard called Optimally Flexible Architecture that can best address these differences. (See article by Cary Millsap, ORACLE Magazine Sep/Oct 95 and Nov/Dec 95)

Enough of the background, the structure I often go with for database setup is as follows

Tablespace Name Purpose

SYSTEM                  Data dictionary only
TEMP                    Temporary tablespace for sorting only


                        (i.e. alter user Everyone temporary tablespace TEMP;)


TOOLS                   Development tools, SQL*Plus catalog, etc., etc.


                        (i.e. alter user SYSTEM default tablespace TOOLS;)


USERS                   Default tablespace for all developers and users.  Used
mainly
                        for adhoc object creation.


                        (i.e. alter user Joe default tablespae USERS;)


RBS                     Tablespace for rollback segments only.
--------------------------------------------------------
APP_DATAx               Sample Name.  If I have several functional modules, I may
                        have a data tablespace for each module.
APP_DATAy               Sample Name.  If I have several functional modules, I may
                        have an index tablespace for each module.

I also create an Oracle user that acts as a schema owner for the application and point its
default tablespace to APP_DATAx.

The reason for this segregation is for maintenance. Often, the activity within a tablespace that is more volatile (i.e. TEMPorary segments or RollBack Segments) begins to fragment the tablespace causing the RDBMS to work harder by forcing it to coalesce adjacent extents when a request requires more space. If the tablespaces are not segmented by type (i.e. a user's temporary sort space and application data are in the same tablespace), the RDBMS may not find enough space to satisy a request because the tablespace is so fragmented (e.g. honeycombed effect where there is several small chunks but none adjacent) yet its overall free space may have enough to satisfy the request.

2) Does tablespaces only simplyfy admn. operations on objects ? What are the

   other advantages of having tablespaces (other than security of remaining    applications once a specific application get corrupted) ? In other words,    why are tablespaces required ? Can't we do without them taking Database as    an only entity ?

The database is a collection of datafiles grouped logically by a tablespace. In fact, the first tablespace you create via the "create database" statement is the SYSTEM tablespace containing the data dictionary owned by the user SYS.

The use of tablespaces are for logical placement of objects. Ideally, if you can accurately estimate size and growth, a one file-one tablespace configuration can allow you to determine which tablespaces are "volatile|hot". From there, based on the contents of the tablespace, you can determine which module is generating high activity, and possibly, find some tuning opportunities.

Joe Diosana
National ProSource
jdiosana_at_nettap.com
101612,2145_at_compuserve.com Received on Tue Feb 13 1996 - 09:39:48 CST