CREATE TABLESPACE

Purpose

Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects.

• A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in datafiles.

• An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.

• A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.

When you create a tablespace, it is initially a read/write tablespace. You can subsequently use the ALTER TABLESPACE statement to take the tablespace offline or online, add datafiles or tempfiles to it, or make it a read-only tablespace.

You can also drop a tablespace from the database with the DROP TABLESPACE statement.

Additional Topics

• Prerequisites

• Syntax

• Semantics

• Examples

Prerequisites

You must have the CREATE TABLESPACE system privilege. To create the SYSAUX tablespace, you must have the SYSDBA system privilege.

Before you can create a tablespace, you must create a database to contain it, and the database must be open.

To use objects in a tablespace other than the SYSTEM tablespace:

• If you are running the database in automatic undo management mode, then at least one UNDO tablespace must be online.

• If you are running the database in manual undo management mode, then at least one rollback segment other than the SYSTEM rollback segment must be online.

Syntax

create_tablespace::=

(permanent_tablespace_clause, temporary_tablespace_clause, undo_tablespace_clause)

permanent_tablespace_clause::=

(file_specification::=, size_clause::=, logging_clause::=, encryption_spec::=, table_compression::=, storage_clause::=, extent_management_clause ::=, segment_management_clause ::=, flashback_mode_clause ::=)

logging_clause::=

encryption_spec::=

table_compression::=

extent_management_clause ::=

(size_clause::=)

segment_management_clause ::=

flashback_mode_clause ::=

temporary_tablespace_clause::=

(file_specification::=, tablespace_group_clause, extent_management_clause ::=)

tablespace_group_clause::=

undo_tablespace_clause::=

(file_specification::=, extent_management_clause ::=, tablespace_retention_clause ::=)

tablespace_retention_clause ::=

Semantics

BIGFILE | SMALLFILE

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

• A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (2³²) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

• A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2²²) blocks.

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restrictions on Bigfile Tablespaces Bigfile tablespaces are subject to the following restrictions:

• You can specify only one datafile in the DATAFILE clause or one tempfile in the TEMPFILE clause.

• You cannot specify EXTENT MANAGEMENT DICTIONARY.

permanent_tablespace_clause

Use the following clauses to create a permanent tablespace. (Some of these clauses are also used to create a temporary or undo tablespace.)

tablespace

Specify the name of the tablespace to be created.

Note on the SYSAUX Tablespace SYSAUX is a required auxiliary system tablespace. You must use the CREATE TABLESPACE statement to create the SYSAUX tablespace if you are upgrading from a release prior to Oracle Database 11g. You must have the SYSDBA system privilege to specify this clause, and you must have opened the database in MIGRATE mode.

You must specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO for the SYSAUX tablespace. The DATAFILE clause is optional only if you have enabled Oracle-managed files. See "DATAFILE | TEMPFILE Clause" for the behavior of the DATAFILE clause.

Take care to allocate sufficient space for the SYSAUX tablespace. For guidelines on creating this tablespace, refer to Oracle Database Upgrade Guide.

Restrictions on the SYSAUX Tablespace You cannot specify OFFLINE or TEMPORARY for the SYSAUX tablespace.

DATAFILE | TEMPFILE Clause

Specify the datafiles to make up the permanent tablespace or the tempfiles to make up the temporary tablespace. Use the datafile_tempfile_spec form of file_specification to create regular datafiles and tempfiles in an operating system file system or to create Automatic Storage Management disk group files.

You must specify the DATAFILE or TEMPFILE clause unless you have enabled Oracle-managed files by setting a value for the DB_CREATE_FILE_DEST initialization parameter. For Automatic Storage Management disk group files, the parameter must be set to a multiple file creation form of Automatic Storage Management filenames. If this parameter is set, then the database creates a system-named 100 MB file in the default file destination specified in the parameter. The file has AUTOEXTEND enabled and an unlimited maximum size.

Notes on Specifying Datafiles and Tempfiles 

• For operating systems that support raw devices, the REUSE keyword of datafile_tempfile_spec has no meaning when specifying a raw device as a datafile. Such a CREATE TABLESPACE statement will succeed whether or not you specify REUSE.

• You can create a tablespace within an Automatic Storage Management disk group by providing only the disk group name in the datafile_tempfile_spec. In this case, Automatic Storage Management creates a datafile in the specified disk group with a system-generated filename. The datafile is auto-extensible with an unlimited maximum size and a default size of 100 MB. You can use the autoextend_clause to override the default size.

• If you use one of the reference forms of the ASM_filename, which refers to an existing file, then you must also specify REUSE.

MINIMUM EXTENT Clause

This clause is valid only for a dictionary-managed tablespace. Specify the minimum size of an extent in the tablespace. This clause lets you control free space fragmentation in the tablespace by ensuring that the size of every used or free extent in a tablespace is at least as large as, and is a multiple of, the value specified in the size_clause.

BLOCKSIZE Clause

Use the BLOCKSIZE clause to specify a nonstandard block size for the tablespace. In order to specify this clause, the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter must be set, and the integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

Restriction on BLOCKSIZE You cannot specify nonstandard block sizes for a temporary tablespace or if you intend to assign this tablespace as the temporary tablespace for any users.

logging_clause

Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.

The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

FORCE LOGGING

Use this clause to put the tablespace into FORCE LOGGING mode. Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any NOLOGGING setting for individual objects. The database must be open and in READ WRITE mode.

This setting does not exclude the NOLOGGING attribute. You can specify both FORCE LOGGING and NOLOGGING. In this case, NOLOGGING is the default logging mode for objects subsequently created in the tablespace, but the database ignores this default as long as the tablespace or the database is in FORCE LOGGING mode. If you subsequently take the tablespace out of FORCE LOGGING mode, then the NOLOGGING default is once again enforced.

Examples

These examples assume that your database is using 8K blocks.

Creating a Bigfile Tablespace: Example The following example creates a bigfile tablespace bigtbs_01 with a datafile bigtbs_f1.dat of 10 MB:

CREATE BIGFILE TABLESPACE bigtbs_01
  DATAFILE 'bigtbs_f1.dat'
  SIZE 20M AUTOEXTEND ON;

Creating an Undo Tablespace: Example The following example creates a 10 MB undo tablespace undots1:

CREATE UNDO TABLESPACE undots1
   DATAFILE 'undotbs_1a.f'
   SIZE 10M AUTOEXTEND ON
   RETENTION GUARANTEE;

Creating a Temporary Tablespace: Example This statement shows how the temporary tablespace that serves as the default temporary tablespace for database users in the sample database was created:

CREATE TEMPORARY TABLESPACE temp_demo
   TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

Assuming that the default database block size is 2K, and that each bit in the map represents one extent, then each bit maps 2,500 blocks.

The following example sets the default location for datafile creation and then creates a tablespace with an Oracle-managed tempfile in the default location. The tempfile is 100 M and is autoextensible with unlimited maximum size. These are the default values for Oracle-managed files:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TEMPORARY TABLESPACE tbs_05;

Adding a Temporary Tablespace to a Tablespace Group: Example The following statement creates the tbs_temp_02 temporary tablespace as a member of the tbs_grp_01 tablespace group. If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:

CREATE TEMPORARY TABLESPACE tbs_temp_02
  TEMPFILE 'temp02.dbf' SIZE 5M AUTOEXTEND ON
  TABLESPACE GROUP tbs_grp_01;

Creating Basic Tablespaces: Examples This statement creates a tablespace named tbs_01 with one datafile:

CREATE TABLESPACE tbs_01 
   DATAFILE 'tbs_f2.dat' SIZE 40M 
   ONLINE; 

This statement creates tablespace tbs_03 with one datafile and allocates every extent as a multiple of 500K:

CREATE TABLESPACE tbs_03 
   DATAFILE 'tbs_f03.dbf' SIZE 20M
   LOGGING;

Enabling Autoextend for a Tablespace: Example This statement creates a tablespace named tbs_02 with one datafile. When more space is required, 500 kilobyte extents will be added up to a maximum size of 100 megabytes:

CREATE TABLESPACE tbs_02 
   DATAFILE 'diskb:tbs_f5.dat' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

Creating a Locally Managed Tablespace: Example The following statement assumes that the database block size is 2K.

CREATE TABLESPACE tbs_04 DATAFILE 'file_1.f' SIZE 10M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

This statement creates a locally managed tablespace in which every extent is 128K and each bit in the bit map describes 64 blocks.

The following statement creates a locally managed tablespace with uniform extents and shows an example of a table stored in that tablespace:

CREATE TABLESPACE lmt1 DATAFILE 'lmt_file2.f' SIZE 100m REUSE
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE TABLE lmt_table1 (col1 NUMBER, col2 VARCHAR2(20))
  TABLESPACE lmt1 STORAGE (INITIAL 2m NEXT 1m MINEXTENTS 5 MAXSIZE 100m);

The initial size of the segment is 5M. Five 1M extents are allocated to the segment. The segment's maximum size is limited to 100M.

The following example creates a locally managed tablespace without uniform extents:

CREATE TABLESPACE lmt2 DATAFILE 'lmt_file3.f' SIZE 100m REUSE 
  EXTENT MANAGEMENT LOCAL;

CREATE TABLE lmt_table2 (col1 NUMBER, col2 VARCHAR2(20)) 
  TABLESPACE lmt2 STORAGE (INITIAL 2m NEXT 1m MINEXTENTS 5 MAXSIZE 100m);

The initial segment size if the table is 5M. Oracle Database determines the size of each extent and the total number of extents allocated to satisfy the initial segment size. The segment's maximum size is limited to 100M.

Creating an Encrypted Tablespace: Example The following statement creates an encrypted tablespace. Encryption must first be enabled for the database by opening the wallet:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1";

System altered.

CREATE TABLESPACE encrypt_ts
  DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dat' SIZE 1M
  ENCRYPTION USING '3DES168'
  DEFAULT STORAGE (ENCRYPT);

Tablespace created.

Specifying Segment Space Management for a Tablespace: Example The following example creates a tablespace with automatic segment-space management:

CREATE TABLESPACE auto_seg_ts DATAFILE 'file_2.f' SIZE 1M
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

Creating Oracle-managed Files: Examples The following example sets the default location for datafile creation and creates a tablespace with a datafile in the default location. The datafile is 100M and is autoextensible with an unlimited maximum size:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TABLESPACE omf_ts1;

The following example creates a tablespace with an Oracle-managed datafile of 100M that is not autoextensible:

CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF;