TABLESPACES

TABLESPACES:

• A tablespace may consist of zero or more segments.
• Except for the SYSTEM tablespace or a tablespace with an active undo segment,
tablespaces can be taken offline, leaving the database running.
• Each segment is made up of one or more extents.
The standard data block size for an Oracle database is specified by the
DB_BLOCK_SIZE initialization parameter when the database is created.

Tablespace creation:

CREATE TABLESPACE userdata
DATAFILE '/oradata/imstest/userdata01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

MINIMUM EXTENT ensures that every used extent size in the tablespace is
a multiple of the integer. Use Kor M to specify this
size in kilobytes or megabytes.
LOGGING specifies that, by default, all tables, indexes, and partitions
within the tablespace have all changes written to redo.
LOGGING is the default.
NOLOGGING specifies that, by default, all tables, indexes, and partitions
within the tablespace do not have all changes written to redo.
NOLOGGING affects only some DML and DDL commands,
for example, direct loads.
DEFAULT specifies the default storage parameters for all objects created
in the tablespace creation
OFFLINE makes the tablespace unavailable immediately after creation
PERMANENT specifies that the tablespace can be used to hold permanent
objects
TEMPORARY specifies that the tablespace be used only to hold temporary
objects; for example, segments used by implicit sorts caused
by an ORDER BY clause extent_management_clause
specifies how the extents of the tablespace are managed. This
clause is discussed in a subsequent section of this lesson.
datafile_clause :== filename
[SIZE integer[K|M] [REUSE] | REUSE ] [ autoextend_clause ]
where: filename is the name of a data file in the tablespace
SIZE specifies the size of the file. Use K or M to
specify the size in kilobytes or megabytes.
REUSE allows the Oracle server to reuse an existing file
autoextend_clause enables or disables the automatic extension of the data file. This clause is discussed in a subsequent section of this lesson.

Space Management in Tablespaces

• Locally managed tablespaces:
– Free extents recorded in bitmap
– Each bit corresponds to a block or group of blocks
– Bit value indicates free or used
• Dictionary-managed tablespaces:
– Default method
– Free extents recorded in data dictionary tables



Locally Managed Tablespaces

CREATE TABLESPACE userdata
DATAFILE '/oradata/imstest/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

• Reduced contention on data dictionary tables
• No undo generated when space allocation or deallocation occurs
• No coalescing required

DICTIONARY specifies that the tablespace is managed using
dictionary tables.
LOCAL specifies that tablespace is locally managed
with a bitmap. If you specify LOCAL, you
cannot specify DEFAULT storage_clause,
MINIMUM EXTENT, or TEMPORARY.
AUTOALLOCATE specifies that the tablespace is system managed.
Users cannot specify an extent size.
This is the default.