Oracle CREATE TABLESPACE Insights

Tablespaces are used to store database objects that take up space on disks. These objects are called segments. Examples of segments include tables, indexes, clusters, undo segments, and materialized views. Database objects that do not take up space on disks are stored in the data dictionary. Examples of these objects include triggers, functions, stored procedures, and database links.

Tablespaces are also used to store segments in a logical manner. You could store every database segment in one tablespace. This would be similar to placing all of your workstation’s files in one giant folder. Tablespaces let you better manage your database segments. When segments are in multiple tablespaces, you have more flexibility. For instance, you can:

• Separate a schema’s segments from other schema segments.
• Balance I/O load among different physical disk devices.
• Separate the data dictionary, undo segments, temporary segments, and application segments from interfering with each other.
• Make certain segments READ ONLY by placing them in a READ ONLY tablespace. This protects from unwanted changes to data.
• Backup certain segments by backing up just the tablespace that contains those segments.
• Limit the amount of data a schema can store with tablespace quotas.

Space Allocation Recommendations

There are two ways that you can manage space allocation of segments stored in a tablespace. The old way was to track the allocated space and the free space in a tablespace with the data dictionary. This method is called Dictionary Managed Tablespace, or DMT. Using DMTs required space allocation operations to use two data dictionary tables, SYS.UET$ for tracking used extents, and SYS.FET$ for tracking free extents. These two tables could become a bottleneck for frequent space allocation and deallocation requests.

Starting with the Oracle 8i release, a new space allocation method was created. This method is called Locally Managed Tablespace, or LMT. LMTs track space allocation using bitmaps in the datafile’s header, or locally in the tablespace. The data dictionary tables are no longer bottlenecks for space allocation and deallocation requests.

When creating an LMT, you have two options to define how Oracle creates extents in the tablespace. The first option is to force each extent in the tablespace to be the same exact size. This is UNIFORM space allocation. The other option is to let the database determine the best size for your extent automatically with the AUTOALLOCATE option. With AUTOALLOCATE, the database first starts allocating smaller extents. After you’ve requested a number of small extents, the database starts allocating larger extents. Early in LMTs history, many Oracle experts would only use UNIFORM space allocation. But more and more, these experts are now switching to using AUTOALLOCATE exclusively.

Oracle is recommending that all tablespaces be created as LMTs. Future versions of Oracle will require doing away with DMTs, leaving LMTs as the only space allocation method for tablespaces. The Oracle 9.2.0 release was the first one that let the SYSTEM tablespace become an LMT. If you choose to create the SYSTEM tablespace as an LMT, then you cannot have any DMTs in your database. If you want DMTs in your database, then your SYSTEM tablespace must be a DMT.

Creation Recommendations

• As stated above, it is recommended that a Locally Managed Tablespace be created. You can use either allocation method. With UNIFORM extent sizes, each extent in the tablespace will be exactly the same size. With AUTOALLOCATE, the database decides the best size for the extent. The AUTOALLOCATE relieves the DBA from the task of determining the best size for the extents.
   
• If COMPATIBLE is set to 9.0.0 or higher, the default space management is Locally Managed. If COMPATIBLE is set less than 9.0.0, the default space management is set to Dictionary Managed. The defaults can be overridden by the EXTENT MANAGEMENT clause.
   
• The UNDO option was introduced in Oracle 9i to create a tablespace to hold system-managed undo segments. This relieves the DBA from the task of determining the optimal settings and the number of rollback segments. In order to use UNDO tablespaces, the UNDO_MANAGEMENT parameter should be set to AUTO. The UNDO_TABLESPACE needs to be set to a tablespace name that we specifically set for undo segments. You will not be able to create objects in this tablespace. The database will automatically create undo segments for you.
   
• Beginning with the Oracle 9i version, the BLOCKSIZE parameter lets you create tablespaces with block sizes that differ from your database’s default block, specified by the DB_BLOCK_SIZE parameter. In order to use this option, you need to set DB_CACHE_SIZE instead of the older DB_BLOCK_BUFFERS and you need to have DB_nK_CACHE_SIZE set, where ‘n’ is the block size you specify above.
   
• The NOLOGGING clause is often misunderstood. Many people take this to mean that any changes to the data in segments stored in this tablespace will not be logged to the database’s online redo logs. When the tablespace is set to NOLOGGING, only certain operations (creation of objects, direct path inserts, and direct loads of SQL*Loader) are not logged. Regular DML statements (INSERT, UPDATE, DELETE) are always logged.

Sizing Insights

• Tablespaces should be sized to contain all of the objects they are intended to hold. This means that in order to size a tablespace properly you will need to size all of the objects that will be placed in the tablespace first. If the tablespace will contain large objects (LOBs) it may be difficult to get an accurate size estimate.
   
• The AUTOEXTEND capability can make some tablespace management easier but it is no replacement for properly sizing tablespaces. One problem with the AUTOEXTEND is that you have no way of knowing when you will run out of space due to a runaway process (for example a cartesion product of two million row tables filling the temporary tablespace ). 
   
• Once you have the sizes of all the objects that will reside in a tablespace, add these size figures together and add 25-50% additional room to allow for growth. We recommend that the default storage parameters for a tablespace not be used except in the case where the tablespace purpose is to hold rollback segments.

Datafile Insights

• Tablespaces provide storage for all database objects such as tables, indexes and clusters. To create a tablespace contiguous space must be available in order to create a tablespace datafile.
   
• The maximum number of tablespaces for a database is controlled by the maximum number of datafiles. The maximum number of datafiles is controlled via the MAXDATAFILES parameter in the CREATE DATABASE command. Ensure it is set properly or you may have to re-create the database or the control files.
   
• If you add new data files to a tablespace and do not fully specify the filenames, ORACLE creates the data files in the default directory of the database server. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files; the old files will be overwritten and all of the data lost.

Process Insight

• The first tablespace, SYSTEM, is automatically created when the database instance is created. Tablespaces subsequent to SYSTEM must be created manually (unless your database has been provided by a third party vendor, in that case, the database instance tablespaces may already be created with SYSTEM).
   
• You should never leave SYSTEM as the only tablespace as this can lead to data corruption and system failure.