Problem
You realize that tablespaces are the logical containers for database objects such as tables and indexes. Furthermore, you’re aware that if you don’t specify storage attributes when creating objects, then the tables and indexes automatically inherit the storage characteristics of the tablespaces (that the tables and indexes are created within). Therefore you want to create tablespaces in a manner that maximizes table performance and maintainability.
Solution
We recommend that you create your tablespaces with the locally managed and automatic segment space management features (ASSM) enabled. This is the default behavior starting with Oracle Database 12c:
create tablespace tools
datafile '/u01/dbfile/O12C/tools01.dbf' size 100m;You can verify that the tablespace was created locally managed and is using ASSM via this query:
select tablespace_name, extent_management, segment_space_management
from dba_tablespaces
where tablespace_name='TOOLS';Here is some sample output:
TABLESPACE_NAME EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT
---------------- ----------------------- -------------------------
TOOLS LOCAL AUTO
How It Works
To be clear, this recipe discusses two separate desirable tablespace features:
- Locally managed tablespaces
- Automatic Segment Space Management (ASSM)
Starting with Oracle Database 12c, all tablespaces are created as locally managed. In prior versions of Oracle you had the choice of either locally managed or dictionary managed. Going forward you should always use locally managed tablespaces.
The tablespace segment space management feature can be set to either AUTO (the default) or MANUAL. Oracle strongly recommends that you use AUTO (referred to as ASSM). This allows Oracle to automatically manage many physical space characteristics that the DBA had to previously manually adjust. In most scenarios, an ASSM managed tablespace will process transactions more efficiently than a MANUAL segment space management enabled tablespace. There are a few corner cases where this may not be true. We recommend that you use ASSM unless you have a proven test case where MANUAL is better.
Note
You cannot create the SYSTEM tablespace with the ASSM feature. Also, the ASSM feature is valid only for permanent, locally managed tablespaces.
When creating a tablespace, if you don’t specify a uniform extent size, then Oracle will automatically allocate extents is sizes of 64 KB, 1 MB, 8 MB, and 64 MB. Use the auto-allocation behavior if the objects in the tablespace typically are of varying size. You can explicitly tell Oracle to automatically determine the extent size via the EXTENT MANAGEMENT LOCAL AUTOALLOCATE clause.
You can choose to have the extent size be consistently the same for every extent within the tablespace via the UNIFORM SIZE clause. This example uses a uniform extent size of 128k:
create tablespace tools
datafile '/u01/dbfile/O12C/tools01.dbf' size 100m
extent management local uniform size 128k;If you have a good reason to set the extent size to a uniform size, then by all means do that. However, if you don’t have justification, take the default of AUTOALLOCATE.
You can also specify that a datafile automatically grow when it becomes full. This is set through the AUTOEXTEND ON clause. If you use this feature, we recommend that you set an overall maximum size for the datafile. This will prevent runaway or erroneous SQL from accidentally consuming all available disk space (think about what could happen with a cloud service that automatically adds disk space as required for a database). Here’s an example:
create tablespace tools
datafile '/u01/dbfile/O12C/tools01.dbf' size 100m
autoextend on maxsize 10G;
