只问耕耘
分类: Oracle
2007-08-20 10:55:37
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.
You must have CREATE
TABLESPACE
system privilege. Also, the SYSTEM
tablespace must contain at least two rollback segments including the SYSTEM
rollback segment.
Before you can create a tablespace, you must create a database to contain it, and the database must be open.
See Also: |
|
filespec
: See .
::=
::=
storage_clause
: See .
::=
tablespace
Specify the name of the tablespace to be created.
DATAFILE
filespec
Specify the datafile or files to make up the tablespace.
Note: For operating systems that support raw devices, the |
|
See Also: |
|
autoextend_clause
Use the autoextend_clause
to enable or disable the automatic extension of the datafile.
|
Specify |
|
|
Specify |
|
|
Specify the disk space to allocate to the datafile when more extents are required. |
|
|
The |
|
|
|
|
|
|
|
MINIMUM
EXTENT
integer
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
.
Note: This clause is not relevant for a dictionary-managed temporary tablespace. |
|
See Also: for more information about using |
|
LOGGING
| NOLOGGING
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.
DEFAULT
storage_clause
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: for information on storage parameters |
|
ONLINE
| OFFLINE
|
Specify |
|
|
Specify |
|
|
The data dictionary view |
|
PERMANENT
| TEMPORARY
|
Specify |
|
|
Specify
Restriction: If you specify |
|
extent_management_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. |
|
|
Specify |
|
|
Specify
|
|
|
If you do not specify either
Restriction: If you specify |
|
|
|
DEFAULT
Storage Example
This statement creates a tablespace named tabspace_2
with one datafile:
CREATE TABLESPACE tabspace_2
DATAFILE 'diska: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_3
with one datafile. When more space is required, 50 kilobyte extents will be added up to a maximum size of 10 megabytes:
CREATE TABLESPACE tabspace_5
DATAFILE 'diskb:tabspace_file3.dat' SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 10M;
MINIMUM
EXTENT
Example
This statement creates tablespace tabspace_5
with one datafile and allocates every extent as a multiple of 64K:
CREATE TABLESPACE tabspace_3
DATAFILE 'tabspace_file5.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_1 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.