||Oracle Tips by Burleson
Carrying through with the analogy that Oracle is an operating
system, we can say that tablespaces take the place of disks. But
with this “disk,” you, the Remote DBA, can specify its size and how it will
create and store data (via the DEFAULT STORAGE clause) in its files
3.1 Tablespace Creation
See Code Depot
Keywords and ParametersOracle9i creates a system-managed UNDO
(rollback, for us old-timers) tablespace (this feature is new with
this version). If a database is created in UNDO automatic management
mode, and no UNDO tablespace is specified in the CREATE DATABASE
command, the SYSTEM tablespace will be used. AN UNDO tablespace uses
AUTOALLOCATE LOCAL extent management. The database manages an UNDO
tablespace, and no other objects can be assigned to it. You can only
include the DATAFILE and EXTENT MANAGMEMENT LOCAL clauses for an
UNDO tablespace. All UNDO tablespaces are permanent, read/write, and
are in logging mode; and the values for MINIMUM EXTENT and DEFAULT
STORAGE are system-generated.
tablespace. The name of the tablespace to be created.
DATAFILE. Specifies the datafile or files to comprise the
TEMPFILE. IF tablespace is TEMPORARY, must use TEMPFILE;
specifies the tempfiles to be used in the TEMPORARY tablespace.
MINIMUM EXTENT integer. Controls freespace fragmentation in the
tablespace by ensuring that every in-use and/or free extent size in
a tablespace is at least as large as, and is a multiple of, integer.
Enables or disables the automatic extension of datafile.
OFF. Disables AUTOEXTEND if it is turned on. NEXT and MAXSIZE are
set to zero. Values for NEXT and MAXSIZE must be respecified in
later ALTER TABLESPACE AUTOEXTEND commands if OFF is specified; they
are not persistent values.
ON. Enables AUTOEXTEND.
NEXT. Disk space to allocate to the datafile when more extents are
MAXSIZE. Maximum disk space allowed for allocation to the datafile.
UNLIMITED. Set no limit on allocating disk space to the datafile.
LOGGING, NOLOGGING. Specifies the default logging attributes of all
tables, index, and partitions within the tablespace. LOGGING is the
default. If NOLOGGING is specified, no undo and redo logs are
generated for operations that support the NOLOGGING option on the
tables, index, and partitions within the tablespace. The tablespace-level
logging attribute can be overridden by logging specifications at the
table, index, and partition levels.