Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
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.
See Also:
|
Additional Topics
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.
Note: Oracle strongly recommends that you run your database in automatic undo management mode. For more information, please refer to Oracle Database Administrator's Guide. |
Syntax
create_tablespace::=
(permanent_tablespace_clause, temporary_tablespace_clause, undo_tablespace_clause)
permanent_tablespace_clause::=
(file_specification::=, size_clause::=, logging_clause::=, table_compression::=, storage_clause::=, extent_management_clause ::=, segment_management_clause ::=, flashback_mode_clause ::=)
See Also: "PERMANENT | TEMPORARY Clauses" for information on these keywords, which were part of the preceding syntax in earlier releases |
logging_clause::=
table_compression::=
temporary_tablespace_clause::=
(file_specification::=, tablespace_group_clause, extent_management_clause ::=)
undo_tablespace_clause::=
(file_specification::=, extent_management_clause ::=, tablespace_retention_clause ::=)
tablespace_retention_clause ::=
Semantics
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 232 or 4G 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 222 or 4M 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 10g. 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, please refer to Oracle Database Upgrade Guide.
Restrictions on the SYSAUX Tablespace You cannot specify OFFLINE
or TEMPORARY
for the SYSAUX
tablespace.
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 diskgroup 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.
Note: Media recovery does not recognize tempfiles. |
See Also:
|
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
.
Note: On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation. |
See Also:
|
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
.
See Also: size_clause for information on that clause and Oracle Database Concepts for more information about usingMINIMUM EXTENT to control fragmentation |
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_
n
K_CACHE_SIZE
parameter must be set, and the integer you specify in this clause must correspond with the setting of one DB_
n
K_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.
See Also: Oracle Database Reference for information on theDB_ n K_CACHE_SIZE parameter and Oracle Database Administrator's Guide for information on allowing multiple block sizes in the buffer cache and for restrictions on using multiple block sizes in partitioned objects |
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.
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. That is, 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.
Note: FORCE LOGGING mode can have performance effects. Please refer to Oracle Database Administrator's Guide for information on when to use this setting. |
Restriction on Forced Logging You cannot specify FORCE
LOGGING
for an undo or temporary tablespace.
This clause is valid only for a dictionary-managed tablespace. Oracle strongly recommends that you create tablespaces that are locally managed rather than dictionary managed.
This clause lets you specify default storage parameters for all objects created in the tablespace and default compression of data for all tables created in the tablespace. This clause is not valid for a temporary tablespace.
See Also:
|
ONLINE | OFFLINE Clauses
Use these clauses to determine whether the tablespace is online or offline. This clause is not valid for a temporary tablespace.
ONLINE Specify ONLINE
to make the tablespace available immediately after creation to users who have been granted access to the tablespace. This is the default.
OFFLINE Specify OFFLINE
to make the tablespace unavailable immediately after creation.
The data dictionary view DBA_TABLESPACES
indicates whether each tablespace is online or offline.
PERMANENT | TEMPORARY Clauses
In earlier releases, you could specify the TEMPORARY
keyword after specifying the tablespace name to create a temporary tablespace. (The PERMANENT
keyword was available for syntactic consistency.) This syntax has been deprecated. It is still supported in case you are using dictionary-managed tablespaces, which are not supported by the CREATE
TEMPORARY
TABLESPACE
syntax. If you do use this syntax, you cannot specify the EXTENT
MANAGEMENT
LOCAL
clause or the BLOCKSIZE
clause.
Oracle strongly recommends that you create locally managed temporary tablespaces containing tempfiles by using the temporary_tablespace_clause
. The creation of new dictionary-managed tablespaces is scheduled for desupport.
The extent_management_clause
lets you specify how the extents of the tablespace will be managed.
Note: After you have specified extent management with this clause, you can change extent management only by migrating the tablespace. |
Specify LOCAL
if you want the tablespace to be locally managed. Locally managed tablespaces have some part of the tablespace set aside for a bitmap. This is the default for permanent tablespaces. Temporary tablespaces are always automatically created with locally managed extents.
AUTOALLOCATE
specifies that the tablespace is system managed. Users cannot specify an extent size. You cannot specify AUTOALLOCATE
for a temporary tablespace.
UNIFORM
specifies that the tablespace is managed with uniform extents of SIZE
bytes.The default SIZE
is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM
in order to specify SIZE
. You cannot specify UNIFORM
for an undo tablespace.
Specify DICTIONARY
if you want the tablespace to be managed using dictionary tables.
Restriction on Dictionary-managed Tablespaces You cannot specify DICTIONARY
if the SYSTEM
tablespace of the database is locally managed or if you have specified the temporary_tablespace_clause
.
Note: Oracle strongly recommends that you create only locally managed tablespaces. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces. The creation of new dictionary-managed tablespaces is scheduled for desupport. |
If you do not specify the extent_management_clause
, then Oracle Database interprets the MINIMUM
EXTENT
clause and the DEFAULT
storage_clause
to determine extent management.
If you do not specify the DEFAULT
storage_clause
, then the database creates a locally managed autoallocated tablespace.
If you did specify the DEFAULT
storage_clause
, then:
If you specified the MINIMUM
EXTENT
clause, then the database evaluates whether the values of MINIMUM
EXTENT
, INITIAL
, and NEXT
are equal and the value of PCTINCREASE
is 0. If they are equal, then the database creates a locally managed uniform tablespace with extent size = INITIAL
. If the MINIMUM
EXTENT
, INITIAL
, and NEXT
parameters are not equal, or if PCTINCREASE
is not 0, then the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
If you did not specify MINIMUM
EXTENT
clause, then the database evaluates only whether the storage values of INITIAL
and NEXT
are equal and PCTINCREASE
is 0. If they are equal, then the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.
Restrictions on Extent Management Extent management is subject to the following restrictions:
A permanent locally managed tablespace can contain only permanent objects. If you need a locally managed tablespace to store temporary objects, for example, if you will assign it as a user's temporary tablespace, then use the temporary_tablespace_clause
.
If you specify LOCAL
, then you cannot specify DEFAULT
storage_clause,
MINIMUM
EXTENT
, or the temporary_tablespace_clause
.
See Also: Oracle Database Upgrade Guide for information on changing extent management by migrating tablespaces and "Creating a Locally Managed Tablespace: Example" |
The segment_management_clause
is relevant only for permanent, locally managed tablespaces. It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace.
AUTO Specify AUTO
if you want the database to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO
, then the database ignores any specification for PCTUSED
, FREELIST
, and FREELIST
GROUPS
in subsequent storage specifications for objects in this tablespace. This setting is called automatic segment-space management and is the default.
MANUAL Specify MANUAL
if you want the database to manage the free space of segments in the tablespace using free lists. Oracle strongly recommends that you do not use this setting and that you create tablespaces with automatic segment-space management.
To determine the segment management of an existing tablespace, query the SEGMENT_SPACE_MANAGEMENT
column of the DBA_TABLESPACES
or USER_TABLESPACES
data dictionary view.
Notes: If you specifyAUTO segment management, then:
|
Restrictions on Automatic Segment-space Management This clause is subject to the following restrictions:
You can specify this clause only for a permanent, locally managed tablespace.
You cannot specify this clause for the SYSTEM
tablespace.
See Also:
|
Use this clause in conjunction with the ALTER
DATABASE
FLASHBACK
clause to specify whether the tablespace can participate in FLASHBACK
DATABASE
operations. This clause is useful if you have the database in FLASHBACK
mode but you do not want Oracle Database to maintain Flashback log data for this tablespace.
This clause is not valid for temporary or undo tablespaces.
FLASHBACK ON Specify FLASHBACK
ON
to put the tablespace in FLASHBACK
mode. Oracle Database will save Flashback log data for this tablespace and the tablespace can participate in a FLASHBACK
DATABASE
operation. If you omit the flashback_mode_clause
, then FLASHBACK
ON
is the default.
FLASHBACK OFF Specify FLASHBACK
OFF
to take the tablespace out of FLASHBACK
mode. Oracle Database will not save any Flashback log data for this tablespace. You must take the datafiles in this tablespace offline or drop them prior to any subsequent FLASHBACK
DATABASE
operation. Alternatively, you can take the entire tablespace offline. In either case, the database does not drop existing Flashback logs.
Note: TheFLASHBACK mode of a tablespace is independent of the FLASHBACK mode of an individual table. |
See Also:
|
Use this clause to create a locally managed temporary tablespace, which is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure.
The transient data can be user-generated schema objects such as temporary tables or system-generated data such as temp space used by hash joins and sort operations. When a temporary tablespace, or a tablespace group of which this tablespace is a member, is assigned to a particular user, then Oracle Database uses the tablespace for sorting operations in transactions initiated by that user.
The TEMPFILE
clause is described in "DATAFILE | TEMPFILE Clause". The extent_management_clause
is described in extent_management_clause .
tablespace_group_clause
This clause is relevant only for temporary tablespaces. Use this clause to determine whether tablespace
is a member of a tablespace group. A tablespace group lets you assign multiple temporary tablespaces to a single user and increases the addressability of temporary tablespaces.
Specify a group name to indicate that tablespace
is a member of this tablespace group. The group name cannot be the same as tablespace
or any other existing tablespace. If the tablespace group already exists, then Oracle Database adds the new tablespace to that group. If the tablespace group does not exist, then the database creates the group and adds the new tablespace to that group.
Specify an empty string (' ') to indicate that tablespace
is not a member of any tablespace group.
See Also:
|
Restrictions on Temporary Tablespaces The data stored in temporary tablespaces persists only for the duration of a session. Therefore, only a subset of the CREATE
TABLESPACE
clauses are relevant for temporary tablespaces. The only clauses you can specify for a temporary tablespace are the TEMPFILE
clause, the tablespace_group_clause
, and the extent_management_clause
.
Specify UNDO
to create an undo tablespace. When you run the database in automatic undo management mode, Oracle Database manages undo space using the undo tablespace instead of rollback segments. This clause is useful if you are now running in automatic undo management mode but your database was not created in automatic undo management mode.
Oracle Database always assigns an undo tablespace when you start up the database in automatic undo management mode. If no undo tablespace has been assigned to this instance, then the database uses the SYSTEM
rollback segment. You can avoid this by creating an undo tablespace, which the database will implicitly assign to the instance if no other undo tablespace is currently assigned.
The DATAFILE
clause is described in "DATAFILE | TEMPFILE Clause". The extent_management_clause
is described in extent_management_clause .
This clause is valid only for undo tablespaces.
RETENTION
GUARANTEE
specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace
even if doing so forces the failure of ongoing operations that need undo space in those segments. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.
RETENTION
NOGUARANTEE
returns the undo behavior to normal. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. This is the default.
Restrictions on Undo Tablespaces Undo tablespaces are subject to the following restrictions:
You cannot create database objects in this tablespace. It is reserved for system-managed undo data.
The only clauses you can specify for an undo tablespace are the DATAFILE
clause and the extent_management_clause
to specify local extent management. You cannot specify dictionary extent management using the extent_management_clause
. All undo tablespaces are created permanent, read/write, and in logging mode. Values for MINIMUM
EXTENT
and DEFAULT
STORAGE
are system generated.
See Also:
|
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;
If we assume 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.
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;