A tablespace is a logical container in which Oracle stores table and index data. Physically, tablespaces consist of one or more
data files. The CREATE TABLESPACE statement is used to create a tablespace. The following mouseover presents a somewhat simplified version of the syntax:
Create Tablespace
CREATE TABLESPACE
The command to create a new tablespace.
tablespace_name
The name that you want to give the tablespace.
DATAFILE 'filename'
Specifies the name of the first file to create for the tablespace.
SIZE filesize
Specifies the file size in bytes. Optionally follow this number with K or M to specify the size in kilobytes or megabytes.
MINIMUM EXTENT minsize
Specifies a minimum extent size for the tablespace.
LOGGING
Causes the creation of the tablespace to be logged in the database's redo log. This is done by default.
NOLOGGING
Creates the tablespace without generating any redo log entries.
DEFAULT STORAGE storage_clause
Specifies default storage attributes for objects created in the tablespace.
ONLINE
Brings the tablespace online after it is created. This is done by default.
OFFLINE
Creates the tablespace, but leaves it offline.
PERMANENT
Creates a tablespace for permanent objects.
TEMPORARY
Creates a tablespace for temporary objects.
When you create a tablespace, choosing the correct default storage settings is important. These are the settings that are used by default for any objects created in that tablespace. The following slide show describes the various options that can appear in a storage clause:
Storage Options
The storage options that you specify at the tablespace level represent default storage options for objects created in that tablespace.
It is possible to override those options when you create individual objects such as tables and indexes. However, mixing extent sizes and other storage options can make the task of managing space within a tablespace much more difficult. Many database administrators (DBAs) simplify extent management by creating tablespaces with different storage characteristics and requiring that all objects in a tablespace use the default settings. When a new object is created, it is assigned to the most appropriate tablespace of those available. You might, for example, have a tablespace for large tables, one for medium size tables, and another for extremely small tables. In the next lesson, you will learn how to apply quotas for users using a tablespace and how those quotas are enforced.