A tablespace is a logical storage unit - multiple application objects (e.g. tables) can be stored in one tablespace. A database can contain multiple tablespaces.
A tablespace can be online or offline (not accessible), and can contain one or more datafiles, each of which can be online or offline.
There is no relationship between a tablespace and a schema.
Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.
A tablespace can be set to read-only to eliminate the need to perform backup and recovery of large, static portions of the database.
A tablespace can be set to temporary to optimise it for the storage of temporary objects. See the section below for more details.
Tablespaces can use one of two methods to keep track of free and used space: Dictionary-managed or Locally managed - this is chosen when the tablespace is created and cannot be altered at a later date.
When creating an LMT, the storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not required (invalid syntax).
An LMT can have either uniform or variable extent sizes. Variable extents are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.
- For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.
- For system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed:
SEGMENT SPACE MANAGEMENT MANUAL - Use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. (This is the default.)
SEGMENT SPACE MANAGEMENT AUTO - Use bitmaps to automatically manage the free space within segments.CREATE TABLESPACE mytbs1
DATAFILE '/u01/oracle/data/mytbs01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;See also:
dbms_space.space_usage
dba_tablespaces.SEGMENT_SPACE_MANAGEMENT
Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:
- You cannot create a tempfile with the ALTER DATABASE statement.
- You cannot rename a tempfile or set it to read-only.
- Tempfiles are always set to NOLOGGING mode.
- When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
- Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.
Note: this arrangement enables fast tempfile creation and resizing; however, the disk could run of space later when the tempfiles are accessed.
A tablespace that uses the data dictionary to manage its extents has incremental extent sizes, which are determined by the storage parameters INITIAL, NEXT, and PCTINCREASE. These can be adjusted to control the extent sizes. When additional space is needed, the NEXT and PCTINCREASE parameters determine the sizes of new extents.
Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created.
The SYSTEM tablespace always contains the data dictionary tables for the entire database.
All data stored on behalf of stored PL/SQL program units (procedures, functions, packages, and triggers) resides in the SYSTEM tablespace.
The SYSTEM tablespace is always online when the database is open.
A temporary tablespace is optimised for the storage of temporary data - this is most often required when sorting large data sets.
Specify a default temporary tablespace when creating a database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement.
If you drop the default temporary tablespace, the SYSTEM tablespace will be used as default temporary tablespace. However this is not recommended and in future releases, might not be allowed. You cannot make the default temporary tablespace permanent or take it offline.To change a user account to use a non-default temp tablespace
ALTER USER <user_account> SET TEMPORARY TABLESPACE <temp_tbsp>
Moving data by transporting tablespaces can be orders of magnitude faster than either export/import or unload/load of the same data, because transporting a tablespace involves only copying datafiles and integrating the tablespace metadata.
You can move a tablespace from one Oracle database to another (without copying or moving any files) or you can clone (copy) the tablespace.
Transporting tablespaces will only work between databases on the same platform.
When you transport tablespaces you can also move index data, so you do not have to rebuild the indexes after importing or loading the table data.
"If future generations are to remember us with gratitude rather than contempt, we must leave them more than the miracles of technology. We must leave them a glimpse of the world as it was in the beginning, not just after we got through with it” ~ President Lyndon B. Johnson
Related
CREATE TABLESPACE
Oracle Architecture diagram (11g)