||Oracle Tips by Burleson
These are disk space and create-script-related issues. The number of
potential applications will drive the number and size of database
tablespaces above and beyond the eight base tablespaces. You will
see that these are:
- SYSTEM. Contains files owned by the SYS and SYSTEM
- TOOLS. Contains files usually owned by SYSTEM but
that apply to the Oracle developerís toolset; these files
contain base information and details of forms, reports, and
- ROLLBACK. Contains the private rollback segments; its
size will depend on number of rollback segments and expected
transaction size. May also be an Oracle-configured UNDO
tablespace, which means you will also have to plan for how long
UNDO data should be retained to allow for flashback query.
- DEFAULT USER. Tablespace in which users can create
and destroy temporary, nonapplication-related tables such as
those used in SQL*REPORT for intermediate queries.
- TEMPORARY USER. Tablespace for sorts, joins, and
other operations that require temporary disk space for
intermediate operations. If this tablespace is not available,
and default tablespace is not set for each user, these tables
will be created and dropped in the SYSTEM tablespace, resulting
in fragmentation. Additionally, a poorly designed join or overly
ambitious SELECT statement could result in filling the SYSTEM
area and halting the database.
- UNDOTBS. IN Oracle9i databases created from default
templates in the Database Creation Assistant (DBCA) rollback
segments are placed in the UNDO tablespace and are now referred
to as UNDO segments. Oracle uses automated management of these
UNDO segments by default. This UNDOTBS is used by Oracle9i to
hold UNDO segments.
- CWMLITE. This Oracle9i tablespace is used to store
OLAPSYS schema objects for the new Online Analytical Processing
(OLAP) utilities in Oracle9i and is created when the default
templates in the DBCA are used to create a database.
- DRSYS. Used to store CTXUSER and WKSYS schema objects
used in advanced indexing options and workspace management
utilities and is created when the default templates in the DBCA
are used to create a database.
Each application should have its own set of data and index
tablespaces. If there are several small applications, you might want
to put them in a single large tablespace; but if you can avoid this,
it makes application management easier. Each application should also
have its own index tablespace. This results in a simple formula for
determining the number of tablespaces:
5 + 2 times the number of applications expected
Some applications may require multiple tablespaces, for example
where, for performance, you want to separate out large tables from
the rest of the application. In one case, a single application
generated 13 tablespaces. Most applications arenít as complicated as
this and will only require two tablespaces. Of course, the purists
will claim each table should be in its own tablespace, but this
often is overkill. If you are looking at Oracle Applications
installs, there may be over 100 tablespaces configured.
How to size tablespaces is a difficult question to answer because
each tablespace will have unique requirements. Here are some general
- The SYSTEM tablespace, if you split out the tool tables,
should only require 300 to 400 MB of disk space, this has
increased dramatically due to increased Java and other stored
procedures in the Oracle9i system.
- The TOOLS tablespace will depend entirely on the amount of
development you expect. At one site with 16 applications being
developed, nearly 90 MB were required for the TOOLS tables.
- The ROLLBACK tablespace will again be driven by the number
and size of rollback segments you require. The number and size
of rollback segments is driven by the number of transactions per
rollback segment, the number of users, and the maximum size of
nonbatch transactions. With Oracle8i and Oracle9i, you can
create a large rollback segment and leave it offline until it is
needed for a large transaction, and then use the SET TRANSACTION
USE ROLLBACK SEGMENT command to utilize it after bringing it
online. The number of rollback segments is driven by the number
of expected transactions and can be estimated by the equation:
NUMBER OF DML TRANSACTIONS / TRANSACTIONS PER ROLLBACK SEGMENT
- The number of transactions will be driven by the number of
users and types of database operations they will be doing. In
fact, if the Oracle kernel sees a violation of the above
formula, it will bring online any available public rollback
segments. In Oracle9i you should also consider if you wish to
use the UNDO tablespace, which takes the place of the rollback
segments if it is configured. By default Oracle9i sizes the
UNDOTBS at 200 megabytes.
- The DEFAULT USER tablespace size will depend upon the number
of users you want to assign to it and the estimated size of
tables they will be using. In most cases, 10 to 20 MB is
sufficient. If you expect heavy usage, assign quotas to each
- The TEMPORARY USER tablespace should be up to twice the size
of your largest table, if you use RULE-based optimization and up
to four times the size of your largest table for COST-based; it
is also dependent on the number of users and the size of sorts
or joins they perform. An improperly designed join between large
tables can quickly fill a temporary area. For example, an
unrestricted outside join of 2,000 row tables will result in a
1-million-row temporary sort table. If those rows are each
several hundred bytes long, there goes your temporary space.
Unfortunately, there isnít much that can be done other than to
train developers or ad hoc query generators not to do
unrestricted joins of large tables. If a temporary tablespace
gets filled, the users who are assigned to it cannot perform
operations requiring temporary space; or, worse, the temporary
space may be taken from the SYSTEM area. There is a valid
argument for having several temporary areas if you have a large
number of users. In one instance, a 100-MB temporary tablespace
was completely filled by a single multitable outside join using
- The CWMLITE and DRSYS tablespaces are usually sized at
around 20 megabytes by default.
If you have the disk space, placing the TEMPORARY USER
tablespaces on disk assets of their own will improve query and
report performance due to reduction of disk contention, especially
for large reports or queries using disk sorts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.