What is a smallfile tablespace? How is a smallfile
tablespace different from a bigfile tablespace?
A smallfile tablespace is very different from a
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:
DEFAULT BIGFILE TABLESPACE
DEFAULT TEMPORARY TABLESPACE temp;
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
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