Question:
What is a smallfile tablespace? How is a smallfile
tablespace different from a bigfile tablespace?
Answer:
A smallfile tablespace is very different from a
bigfile tablespace.
The smallfile tablespace is the "traditional" Oracle
tablespace, and the distinction was only made in 10g with
the introduction of the bigfile tablespace.
The default for tablespace types is a
smallfile tablespace but the default can be changed at
create database time:
CREATE DATABASE
mydb
SET
DEFAULT BIGFILE TABLESPACE
DEFAULT TEMPORARY TABLESPACE temp;
ALTER DATABASE
mydb
SET DEFAULT SMALLFILE TABLESPACE.
While bigfile tablespaces
suffer from limitations (must be locally managed tablespace
(LMT) with automatic segment-space management (bitmap
freelists)), the old-fashioned smallfile tablespaces the
perfect for almost all types of Oracle data tables and
indexes.
One of the greatest misconceptions about
the smallfile tablespace is that is is only for small data
files. The "traditional" smallfile tablespaces can
hold billions of bytes of data, and smallfile tablespaces
should be used in all cases except true VLDB data warehouse
system that require the specialized features of the bigfile
tablespaces.
