Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE SYNONYM to CREATE TRIGGER, 4 of 6
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.
create_tablespace::=
create_tablespace
filespec: See filespec.
autoextend_clause
maxsize_clause::=
maxsize_clause
storage_clause: See storage_clause.
extent_management_clause
segment_management_clause
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, 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.
Restrictions:
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:
UNDO_MANAGEMENT
parameter
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.
Use the autoextend_clause
to enable or disable the automatic extension of a new datafile or tempfile. If you do not specify this clause, these files are not automatically extended.
Specify ON
to enable autoextend.
Specify OFF
to turn off autoextend if is turned on.
Use the NEXT
clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. Use K
or M
to specify this size in kilobytes or megabytes. The default is the size of one data block.
Use the MAXSIZE
clause to specify the maximum disk space allowed for automatic extension of the datafile.
Use the UNLIMITED
clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.
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_
nK_CACHE_SIZE
parameter set, and the integer you specify in this clause must correspond with the setting of one DB_
nK_CACHE_SIZE
parameter setting.
Restriction: 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, and partitions within the tablespace. LOGGING
is the default.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.
Only the following operations support the NOLOGGING
mode:
INSERT
(serial or parallel), Direct Loader (SQL*Loader)
CREATE
TABLE
... AS
SELECT
, CREATE
INDEX
, ALTER
INDEX
... REBUILD
, ALTER
INDEX
... REBUILD
PARTITION
, ALTER
INDEX
... SPLIT
PARTITION
, ALTER
TABLE
... SPLIT
PARTITION
, and ALTER
TABLE
... MOVE
PARTITION
In NOLOGGING
mode, data is modified with minimal logging (to mark new extents INVALID
and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose the object, you should take a backup after the NOLOGGING
operation.
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.
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.
Restriction: If you specify TEMPORARY
, you cannot specify EXTENT
MANAGEMENT
LOCAL
or the BLOCKSIZE
clause.
The extent_management_clause lets you specify how the extents of the tablespace will be managed.
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.
If you do not specify the extent_management_clause, Oracle interprets the COMPATIBLE
setting, the MINIMUM
EXTENT
clause and the DEFAULT
storage_clause to determine extent management:
COMPATIBLE
initialization parameter is less than 9.0.0, Oracle creates a dictionary managed tablespace. If COMPATIBLE
= 9.0.0 or higher:
DEFAULT
storage_clause at all, Oracle creates a locally managed autoallocated tablespace.
DEFAULT
storage_clause:
MINIMUM
EXTENT
clause, Oracle evaluates whether the values of MINIMUM
EXTENT
, INITIAL
, and NEXT
are equal and the value of PCT_INCREASE
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 PCT_INCREASE
is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
MINIMUM
EXTENT
clause, Oracle evaluates only whether the storage values of INITIAL
and NEXT
are equal and PCT_INCREASE
is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.
Restrictions:
CREATE
TEMPORARY
TABLESPACE
statement.
LOCAL
, you cannot specify DEFAULT
storage_clause, MINIMUM
EXTENT
, or TEMPORARY
.
Oracle9i Database Migration for information on changing extent management by migrating tablespaces
See Also:
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
, Oracle ignores any specification for 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.
Restrictions on AUTO:
SYSTEM
tablespace.
AUTO
segment-managed tablespaces.
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;
DEFAULT
Storage Example
This statement creates a tablespace named tabspace_2
with one datafile:
CREATE TABLESPACE tbs_1 DATAFILE 'tabspace_file2.dat' SIZE 20M DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 999) ONLINE;
AUTOEXTEND
Example
This statement creates a tablespace named tabspace_5
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_2 DATAFILE 'diskb:tabspace_file5.dat' SIZE 500K REUSE AUTOEXTEND ON NEXT 500K MAXSIZE 10M;
MINIMUM
EXTENT
Example
This statement creates tablespace tabspace_3
with one datafile and allocates every extent as a multiple of 64K:
CREATE TABLESPACE tbs_3 DATAFILE 'tabspace_file3.dbf' SIZE 2M MINIMUM EXTENT 64K DEFAULT STORAGE (INITIAL 128K NEXT 128K) LOGGING;
In the following statement, we assume that the database block size is 2K.
CREATE TABLESPACE tbs_4 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 = 'u01/oradata/sample'; 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;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|