Tablespaces

Tablespaces

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.

Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.

There are three types of tablespaces in Oracle:
A.Permanent tablespaces
B.Undo tablespaces
C.Temporary tablespaces

Creating a tablespace
A tablespace is created with the create tablespace sql command.

----Permanent tablespace
create tablespace ts_something logging datafile '/dbf1/ts_sth.dbf' size 32m
autoextend on next 32m maxsize 2048m extent management local;

create tablespace data datafile '/home/oracle/databases/ora10/data.dbf'size 10Mautoextend on maxsize 200M extent management local uniform size 64K;

----Temporary tablespace
create temporary tablespace temp_mtr tempfile '/dbf1/mtr_temp01.dbf' size 32m
autoextend on next 32m maxsize 2048m extent management local;
--- Undo tablespace
create undotablespace ts_undo datafile '/dbf/undo.dbf' size 100M;

Dropping a tablespace
Dropping a tablespace is a structural change to the database that requires a backup.
The extent size of the objects in the tablespace are subject to the tablespace's minimum extent size.

Quotas on tablespaces
Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota.

Tablespace groups
This is a feature that is available with Oracle 10g.

Assigning a tablespace to a tablespace group:

SQL>alter tablespace ts_user tablespace group ts_grp_user;


Renaming Tablespaces
This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

SQL>alter tablespace ts_user rename to ts_user_01;

The system and sysaux tablespace cannot be renamed, though. But that will not be much of a problem, However, read only tablespaces can be renamed.
After renaming a tablespace, the controlfiles should immediatly be backed up.

The system tablespace

Every Oracle database contains a tablespace named SYSTEM that Oracle creates automatically when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
A small database might need only the SYSTEM tablespace; however, it is recommended that you create at least one additional tablespace to store user data separate from data dictionary information. This allows you more flexibility in various database administration operations and can reduce contention among dictionary objects and schema objects for the same datafiles.

Note: The SYSTEM tablespace must always be kept online

The sysaux tablespace
The sysaux tablespace is new with Oracle 10g . It is used to store database components that were stored in the system tablespace in prior releases of the database. Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g. Additionally, it is the place where automatic workload repository stores its information.

occupants
An occupant is a set of (application-) tables within the sysaux tablespace. The occupants can be viewed with the v$sysaux_occupants

Temporary tablespace
A temporary tablespace is a tablespace that can only be used for sort segments. No permanent objects can reside in a temporary tablespace. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists in every instance that performs a sort operation in a given tablespace.
Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment grows by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance

Transportable tablespaces
Transportable tablespaces can quickly be moved across different platforms (with Oracle 10g)
This allows databases to be migrated at almost the speed of a file transfer.
For example, data providers can deploy their data to custumers that run Oracle on different platforms more easily. Also, usually, data marts are on different platforms as the data warehouse. With transportable tablespaces, the data can be 'copied' from the data warehouse to the data mart more efficiently.

Transportable tablespaces require the export transportable tablespaces option.

RMAN> convert tablespace ts_data, ts_idx to platform 'Microsoft Windows NT' format '/temp/%U';

Bigfile tablespaces
This is a feature of Oracle 10g. A bigfile tablespace contains only one datafile (or tempfile) which can be as big as 2^32 (=4GB) blocks
create bigfile tablespace beeeg_ts data file '/o1/dat/beeeg.dbf' size 2T
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management (which is the default setting since Oracle 9i). There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.
The system and sysauxtablespace cannot be created as bigfile tablespace.
Bigfile tablespaces should be used with automatic storage managemet, or other logical volume managers that support dynamically extensible logical volumes, striping and RAID.

Smallfile tablespaces
A smallfile tablespace is a traditional tablespace that can contain up to 1022 datafiles.

Default tablespaces
An Oracle database can be configured with a default tablespace and a default temporary tablespace. These are used for users who are not configured with default tablespaces or default temporary tablespaces .
Displaying tablespaces
The dba_tablespaces view displays all tablespaces along with the respective relevant information.
Space management
Oracle maintains extents for a tablespace. There are two different methods for Oracle to keep track of free and used (occupied) extents:
Dictionary managed tablespace
Locally managed tablespace
A 'bitmap' is stored within the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free or used. The extent sizes are either uniform or autoallocate.

resulting in the following benefits:

1.Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues

2.Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.

3.Simplified space allocation--when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected

4.Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks

EXTENT MANAGEMENT LOCAL AUTOALLOCATE
EXTENT MANAGEMENT LOCAL UNIFORM

Segment Space Management in Locally Managed Tablespaces

A.MANUAL

Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows.

MANUAL is the default.

B. AUTO

This keyword 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.

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

Dictionary managed tablespaces
For dictionary-managed tablespaces, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated, or freed for reuse. Extents are allocated according to the following storage parameters initial next pctincrease .The information about used and free extents is stored in the dictionary.

CREATE TABLESPACE tbsb DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

State of tablespaces
A tablespace is either online (ready to be accessed) or offline (not accessible).
A tablespace becomes offline either
when the DBA explicitely makes the tablespace offline, or
when an error occures while Oracle accesses a datafile within the tablespace.
Offline tablespaces cannot be transported to other databases.

Read Only Tablespaces

The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle never updates the files of a read-only tablespace, and therefore the files can reside on read-only media, such as CD ROMs or WORM drives.

Whenever you create a new tablespace, it is always created as read-write. The READ ONLY option of the ALTER TABLESPACE command allows you to change the tablespace to read-only, making all of its associated datafiles read-only as well. You can then use the READ WRITE option to make a read-only tablespace writeable again.
Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.
You can drop items, such as tables and indexes, from a read-only tablespace, just as you can drop items from an offline tablespace. However, you cannot create or alter objects in a read-only tablespace.