Managing Tablespaces   «Prev  Next»
Lesson 2Creating tablespaces
ObjectiveCreate a tablespace in your database.

Create Database Tablespaces

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 TABLESPACEThe command to create a new tablespace.
tablespace_nameThe name that you want to give the tablespace.
DATAFILE 'filename'Specifies the name of the first file to create for the tablespace.
SIZE filesizeSpecifies the file size in bytes. Optionally follow this number with K or M to specify the size in kilobytes or megabytes.
MINIMUM EXTENT minsizeSpecifies a minimum extent size for the tablespace.
LOGGINGCauses the creation of the tablespace to be logged in the database's redo log. This is done by default.
NOLOGGINGCreates the tablespace without generating any redo log entries.
DEFAULT STORAGE storage_clauseSpecifies default storage attributes for objects created in the tablespace.
ONLINEBrings the tablespace online after it is created. This is done by default.
OFFLINECreates the tablespace, but leaves it offline.
PERMANENTCreates a tablespace for permanent objects.
TEMPORARYCreates 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.

Creating Tablespaces - Exercise

Here is a matching exercise that covers the attributes used in the default storage clause.
Creating Tablespaces - Exercise

Ad Oracle DBA Mentor