Tablespaces

Tablespaces
The data in an Oracle database are stored in tablespaces.
• An Oracle database can be logically grouped into smaller logical areas of space known
as tablespaces.
• A tablespace can belong to only one database at a time.
• Each tablespace consists of one or more operating system files, which are called data files.
• A tablespace may consist of zero or more segments.
• Tablespaces can be brought online while the database is running.
• Except for the SYSTEM tablespace or a tablespace with an active undo segment, tablespaces can be taken offline, leaving the database running.
• Tablespaces can be switched between read-write and read-only status.

Data Files
• Each tablespace in an Oracle database consists of one or more files called data files.
These are physical structures that conform with the operating system on which the Oracle server is running.
• A data file can belong to only one tablespace.
• An Oracle server creates a data file for a tablespace by allocating the specified amount of disk space plus a small amount of overhead.
• The database administrator can change the size of a data file after its creation or can specify that a data file should dynamically grow as objects in the tablespace grow.

Segments
• A segment is the space allocated for a specific logical storage structure within a tablespace. For example, all of the storage allocated to a table is a segment.
• A tablespace may consist of one or more segments.
• A segment cannot span tablespaces; however, a segment can span multiple data files that belong to the same tablespace.
• Each segment is made up of one or more extents.

Extents
Space is allocated to a segment by extents.
• One or more extents make up a segment.
– When a segment is created, it consists of at least one extent.
– As the segment grows, extents get added to the segment.
– The DBA can manually add extents to a segment.
• An extent is a set of contiguous Oracle blocks.
• An extent cannot span a data file but must exist in one data file.

Data Blocks
The Oracle server manages the storage space in the data files in units called Oracle blocks or data blocks.
• At the finest level of granularity, the data in an Oracle database is stored in data blocks.
• Oracle data blocks are the smallest units of storage that the Oracle server can allocate, read, or write.
• One data block corresponds to one or more operating system blocks allocated from an existing data file.
• The standard data block size for an Oracle database is specified by the
DB_BLOCK_SIZE initialization parameter when the database is created.
• The data block size should be a multiple of the operating system block size to avoid unnecessary I/O.
• The maximum data block size is dependent on the operating system.

 

Advantages of Locally Managed Tablespaces
Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:
• Local management avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a undo segment or data dictionary table.
• Because locally managed tablespaces do not record free space in data dictionary tables, it reduces contention on these tables.
• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
• The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed
tablespace.
• Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

 

Segment Space Management in Locally Managed Tablespaces

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed.

The keyword auto tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, andFREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

The following statement creates tablespace lmtbsb with automatic segment-space management:

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M   EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Dictionary Managed Tablespaces• Extents are managed in the data dictionary• Each segment stored in the tablespace can have a different storage clause• Coalescing required

CREATE TABLESPACE userdata

DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 500M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE ( initial 1M NEXT 1M );

Undo Tablespace

• Used to store undo segments

• Cannot contain any other objects

• Extents are locally managed

• Can only use the DATAFILE and EXTENT MANAGEMENT clauses of the CREATE TABLESPACE command

CREATE UNDO TABLESPACE undo1

DATAFILE ‘/u01/oradata/undo101.dbf’ SIZE 40M;

 

Temporary Tablespace

 

• Used for sort operations

• Cannot contain any permanent objects

• Locally managed extents recommended

CREATE TEMPORARY TABLESPACE temp

TEMPFILE ‘/u01/oradata/temp01.dbf’ SIZE 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

 

Dropping Tablespaces

 

• Tablespace removed from data dictionary

• Optionally, contents removed from data dictionary

• OS files can be deleted with the optional AND DATAFILES clause:

DROP TABLESPACE userdata

INCLUDING CONTENTS AND DATAFILES;

 

CHANGING SIZE OF DATAFILE

ALTER DATABASE

DATAFILE ‘/u03/oradata/userdata02.dbf’ RESIZE 200M;

ALTER TABLESPACE app_data

ADD DATAFILE ‘/u01/oradata/userdata03.dbf’ SIZE 200M;

 

Moving Data Files:

ALTER TABLESPACE

• The tablespace must be offline.

• The target data files must exist.

ALTER TABLESPACE userdata

RENAME  DATAFILE ‘/u01/oradata/userdata01.dbf’

TO ‘/u01/oradata/userdata01.dbf’;

ALTER DATABASE

• The database must be mounted.

• The target data file must exist.

ALTER DATABASE RENAME

FILE ‘/u01/oradata/system01.dbf’

TO ‘/u03/oradata/system01.dbf’;

Data dictionary views

DBA_TEMPFILES

DBA_TABLESPACES

DBA_DATA_FILES

V$TABLESPACE

V$TEMPFILE

V$DATAFILE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: