Tablespace

From Wikipedia, the free encyclopedia

A tablespace is a storage location where the actual data underlying database objects can be kept. It is the logical portion of the database used to allocate storage for all DBMS managed objects, such as table data, indexes, or stored procedures. Once created, a tablespace can be referred to by name when creating database objects.

Tablespaces only specify the database storage locations, not the logical database structure, or database schema. For instance, different objects in the same schema may have different underlying tablespaces. Similarly, a tablespace may service objects for more than one schema.

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast SCSI disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower IDE disk.

While it is common for tablespaces to store their data in a filesystem file, some DBMS allow tablespaces to be configured directly over operating system device entries, called raw devices, providing better performance by avoiding the OS filesystem overheads.

[edit] Oracle example

In the Oracle RDBMS, tablespaces are created by the CREATE TABLESPACE command; for example:

CREATE TABLESPACE index1 DATAFILE '/mnt/sda1/index1.dbf' SIZE 100M ;

In Oracle, there are three types of tablespaces:

• Permanent tablespaces are the primary tablespace used to store the user's database objects.

• Undo tablespaces are used by the DBMS to store data that can be used to undo uncommitted transactions, after a transaction is rolled back (either due to failure or explicitly requested rollback).

• Temporary tablespaces are used to store data with a short lifespan. They are used by the DBMS to manage space for database sort operations, and can also be utilized by users for storing global tables temporarily.