Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Advantage of Tablespace

Re: Advantage of Tablespace

From: Joseph Diosana <101612.2145_at_COMPUSERVE.COM>
Date: Tue, 13 Feb 1996 09:25:46 EST
Message-Id: <>

  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
101612, Received on Tue Feb 13 1996 - 09:39:48 CST

Original text of this message