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 TABLESPACE tablespace_name
DATAFILE 'filename' [SIZE filename [K|M]]
[MINIMUM EXTENT minsize [K|M]]
[LOGGING | NOLOGGING]
[DEFAULT STORAGE storage_clause]
[ONLINE | OFFLINE]
[PERMANENT | TEMPORARY];
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:
1) 
 DEFAULT STORAGE 
(INITIAL 50K NEXT 25K ...)

The INITIAL setting controls the size of the initial extent allocated to new objects in the tablespace. This size can be specified in bytes, kilobytes (K), or megabytes (M).
2) Identify the events that trigger the transition between states. 
DEFAULT STORAGE 
(... NEXT 25K MINEXTENTS 2 ...)

The NEXT setting controls the size of the second, and subsequent, extents assigned to an object. The size may be specified in bytes, kilobytes (K), and megabytes (M).
In this code snippet:
  • `NEXT 25K` specifies that the size of the next extent for storage allocation is 25 kilobytes.
  • `MINEXTENTS 2` specifies that at least two extents will be allocated for this storage object.

Draw the statechart diagram.
3) Draw the statechart diagram.
Identify the states.
4) Identify the states.
Identify the states.
5) Identify the states.

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.

Oracle DBA Mentor

Creating Tablespaces - Exercise

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

SEMrush Software 2 SEMrush Banner 2