Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
Use the CREATE
TABLESPACE
statement to create a tablespace, which is an allocation of space in the database that can contain persistent schema objects.
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 to it, or make it a read-only tablespace.
You can also drop a tablespace from the database with the DROP
TABLESPACE
statement.
You can use the CREATE
TEMPORARY
TABLESPACE
statement to create tablespaces that contain schema objects only for the duration of a session.
See Also:
|
You must have CREATE
TABLESPACE
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:
SYSTEM
rollback segment) must be online.UNDO
tablespace must be online.
create_tablespace::=
datafile_tempfile_spec::=
--part of file_specification
, logging_clause::=
, data_segment_compression::=
, storage_clause::=
, extent_management_clause::=
, segment_management_clause::=
)logging_clause::=
data_segment_compression::=
Specify UNDO
to create an undo tablespace. When you run the database in Automatic Undo Management mode, Oracle 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 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 Oracle will use the SYSTEM
rollback segment. You can avoid this by creating an undo tablespace, which Oracle will implicitly assign to the instance if no other undo tablespace is currently assigned.
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:
|
Specify the name of the tablespace to be created.
Specify the datafile or files to make up the tablespace.
The DATAFILE
clause is optional only if the DB_CREATE_FILE_DEST
initialization parameter is set. In this case, Oracle creates a system-named 100MB file in the default file destination specified in the parameter. The file has AUTOEXTEND
enabled and an unlimited maximum size.
See Also:
|
Specify the minimum size of an extent in the tablespace. This clause lets you control free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, integer
.
See Also:
Oracle9i Database Concepts for more information about using |
Use the BLOCKSIZE
clause to specify a nonstandard block size for the tablespace. In order to specify this clause, you must have the DB_CACHE_SIZE
and at least one DB_
n
K_CACHE_SIZE
parameter set, and the integer you specify in this clause must correspond with the setting of one DB_
n
K_CACHE_SIZE
parameter setting.
You cannot specify nonstandard block sizes for a temporary tablespace (that is, if you also specify TEMPORARY
) or if you intend to assign this tablespace as the temporary tablespace for any users.
See Also:
Oracle9i 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.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
See Also:
|
Use this clause to put the tablespace into FORCE
LOGGING
mode. Oracle 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 Oracle 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:
|
You cannot specify FORCE
LOGGING
for an undo or temporary tablespace.
Specify the default storage parameters for all objects created in the tablespace.
For a dictionary-managed temporary tablespace, Oracle considers only the NEXT
parameter of the storage_clause
.
See Also:
|
Specify ONLINE
to make the tablespace available immediately after creation to users who have been granted access to the tablespace. This is the default.
Specify OFFLINE
to make the tablespace unavailable immediately after creation.
The data dictionary view DBA_TABLESPACES
indicates whether each tablespace is online or offline.
Specify PERMANENT
if the tablespace will be used to hold permanent objects. This is the default.
Specify TEMPORARY
if the tablespace will be used only to hold temporary objects, for example, segments used by implicit sorts to handle ORDER
BY
clauses.
Temporary tablespaces created with this clause are always dictionary managed, so you cannot specify the EXTENT
MANAGEMENT
LOCAL
clause. To create a locally managed temporary tablespace, use the CREATE
TEMPORARY
TABLESPACE
statement.
If you specify TEMPORARY
, then you cannot specify the BLOCKSIZE
clause.
The extent_management_clause
lets you specify how the extents of the tablespace will be managed.
Note: Once you have specified extent management with this clause, you can change extent management only by migrating the tablespace. |
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.
AUTOALLOCATE
specifies that the tablespace is system managed. Users cannot specify an extent size. This is the default if the COMPATIBLE
initialization parameter is set to 9.0.0 or higher.UNIFORM
specifies that the tablespace is managed with uniform extents of SIZE
bytes. Use K
or M
to specify the extent size in kilobytes or megabytes. The default SIZE
is 1 megabyte.DICTIONARY
if you want the tablespace to be managed using dictionary tables. This is the default if the COMPATIBLE
initialization parameter is set less than 9.0.0.You cannot specify DICTIONARY
if the SYSTEM
tablespace of the database is locally managed.
If you do not specify the extent_management_clause
, then Oracle interprets the COMPATIBLE
setting, the MINIMUM
EXTENT
clause and the DEFAULT
storage_clause
to determine extent management. If the COMPATIBLE
initialization parameter is less than 9.0.0, then Oracle creates a dictionary managed tablespace. If COMPATIBLE
= 9.0.0 or higher:
DEFAULT
storage_clause
, then Oracle creates a locally managed autoallocated tablespace.DEFAULT
storage_clause
:
MINIMUM
EXTENT
clause, then Oracle evaluates whether the values of MINIMUM
EXTENT
, INITIAL
, and NEXT
are equal and the value of PCTINCREASE
is 0. If so, Oracle 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, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.MINIMUM
EXTENT
clause, then Oracle evaluates only whether the storage values of INITIAL
and NEXT
are equal and PCTINCREASE
is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.
See Also:
Oracle9i Database Concepts for a discussion of locally managed tablespaces |
CREATE
TEMPORARY
TABLESPACE
statement.LOCAL
, then you cannot specify DEFAULT
storage_clause,
MINIMUM
EXTENT
, or TEMPORARY
.
See Also:
Oracle9i Database Migration 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 should track the used and free space in the segments in the tablespace using free lists or bitmaps.
Specify MANUAL
if you want Oracle to manage the free space of segments in the tablespace using free lists.
Specify AUTO
if you want Oracle to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO
, then Oracle 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.
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.
SYSTEM
tablespace.
See Also:
|
The following example creates a 10 MB undo tablespace undots1
with datafile undotbs_1a.f
:
CREATE UNDO TABLESPACE undots1 DATAFILE 'undotbs_1a.f' SIZE 10M AUTOEXTEND ON;
This statement creates a tablespace named tbs_01
with one datafile:
CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dat' SIZE 40M DEFAULT STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 999) ONLINE;
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 10 megabytes:
CREATE TABLESPACE tbs_02 DATAFILE 'diskb:tbs_f5.dat' SIZE 500K REUSE AUTOEXTEND ON NEXT 500K MAXSIZE 100M;
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 MINIMUM EXTENT 500K DEFAULT STORAGE (INITIAL 128K NEXT 128K) LOGGING;
In the following statement, we assume 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 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;
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/log'; 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;