Files in Support of Backup and Recovery Operations - Oracle RMAN for Absolute Beginners - page 40

Creating Tablespaces
You use the CREATE TABLESPACE statement to create tablespaces. The Oracle SQL Reference Manual contains more
than a dozen pages of syntax and examples for creating tablespaces. In most scenarios, you need to use only a few of
the features available, namely, locally managed extent allocation, and automatic segment space management. The
following code snippet demonstrates how to create a tablespace that employs the most common features:
create tablespace tools datafile '/u01/dbfile/O12C/tools01.dbf'
size 100m
segment space management auto;
You need to modify this script for your environment. By default, tablespaces will be created as locally managed.
A locally managed tablespace uses a bitmap in the data file to efficiently determine whether an extent is in use. The
storage parameters NEXT , PCTINCREASE , MINEXTENTS , MAXEXTENTS , and DEFAULT aren't valid for extent options in locally
managed tablespaces.
The SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage the space within the block. When
you use this clause, there is no need to specify parameters, such as PCTUSED , FREELISTS , and FREELIST GROUPS .
The alternative to AUTO space management is MANUAL . When you use MANUAL , you can adjust the parameters to the
requirements of your application. I recommend that you use AUTO and not MANUAL . Using AUTO vastly reduces the
number of parameters you need to configure and manage.
As you create tables and indexes within a tablespace, Oracle will automatically allocate space to segments
within a tablespace's datafile as required. The default type of allocation is to automatically allocate space. You can
explicitly instruct Oracle to use automatic allocation via the AUTOALLOCATE clause. Oracle allocates extent sizes of
64KB, 1MB, 8MB, or 64MB. Using AUTOALLOCATE is appropriate when you think objects in one tablespace will be of
varying sizes (which is often the case). I usually use the default of allowing Oracle to automatically determine the
extent sizes.
The alternative to AUTOALLOCATE is uniform extent sizes. You can instruct Oracle to allocate a uniform size for
each extent via the UNIFORM SIZE [size] clause. If you don't specify a size, then the default uniform extent size is
1MB. The uniform extent size that you use varies, depending on the storage requirements of your tables and indexes.
In some scenarios, I create several tablespaces for a given application. For instance, you can create a tablespace for
small objects that has a uniform extent size of 512KB, a tablespace for medium-sized objects that has a uniform extent
size of 4MB, a tablespace for large objects with a uniform extent size of 16MB, and so on.
When a data file fills up, you can instruct Oracle to increase the size of the data file automatically, with the
AUTOEXTEND feature. I recommend that you don't use this feature. Instead, you should monitor tablespace growth
and add space as necessary. Manually adding space is preferable to having a runaway SQL process that accidentally
grows a tablespace until it has consumed all the space on a mount point. If you inadvertently fill up a mount point that
contains a control file or the Oracle binaries, you can hang your database.
If you do use the AUTOEXTEND feature, I suggest that you always specify a corresponding MAXSIZE so that a runaway
SQL process doesn't accidentally fill up a tablespace that in turn fills up a mount point. Here is an example of creating
an autoextending tablespace with a cap on its maximum size:
create tablespace tools datafile '/u01/dbfile/O12C/tools01.dbf'
size 100m
autoextend on maxsize 1000m
segment space management auto;