Tablespaces are used to organize tables and indexes into manageable groups, tablespaces themselves are made up of one for more data/temp files.
Oracle has 4 different types of tablespace
Every oracle database has at least two tablespaces
Tablespace Management
There are two ways to manage a tablespace
Locally (default)
Dictionary Managed
Extents are the basic unit of a tablespace and are managed in bitmaps that are kept within the data file header for all the blocks within that data file. For example, if a tablespace is made up of 128KB extents, each 128KB extent is represented by a bit in the extent bitmap for this file, the bitmap values indicate if the extent is used or free. The bitmap is updated when the extent changes there is no updating on any data dictionary tables thus increasing performance.
Extents are tracked via bitmaps not using recursive SQL which means a performance improvement.
Locally managed tablespaces cannot be converted into a dictionary managed one. The benefits of using a local managed tablespace
The extent allocation is managed by the data dictionary and thus updating the extent information requires that you access the data dictionary, on heavy used systems this can cause a performance drop.
extents are tracked via FET$ and UET$ using recursive SQL.
Dictionary managed tablespaces can be converted to a locally managed one.
There are a number of things that you should know about tablespaces.
Extent Management
Anytime an object needs to grow in size space is added to that object by extents. When you are using locally managed tablespaces there are two options that the extent size can be managed
Autoallocate (default)
Uniform
This means the extent will vary in size, the first extent starts at 64k and progressively increased to 64MB by the database. The database automatically decides what size the new extent will be based on segment growth patterns.
Autoallocate is useful if you aren't sure about growth rate of an object and you let oracle decide.
Create the extents the same size by specifying the size when create the tablespace.
This is default for temporary tablespace but not available for undo tablespaces.
Be careful with uniform as it can waste space, use this option you are know what the growth rate of the objects are going to be.
Segment Space Management
Segment space management is how oracle deals with free space with in an oracle data block. The segment space management you specify at tablespace creation time applies to all segments you later create in the tablespace.
Oracle uses two methods to deal with free space
Manual
Automatic (default)
Oracle manages the free space in the data blocks by using free lists and a pair of storage parameters PCTFREE and PCTUSED. When the block reaches the PCTUSED percentage the block is then removed from the freelist, when the block falls below the PCTFREE threshold the block is then placed back on the freelist. Oracle has to perform a lot of hard work maintaining these lists, a slow down in performance can occur when you are making lots of changes to the blocks as Oracle needs to keep checking the block thresholds.
Oracle does not use freelist when using automatic mode, Instead oracle uses bitmaps. A bitmap which is contained in a bitmap block, indicates whether free space in a data block is below 25%, between 25%-50%, between 50%-75% or above 75%. For an index block the bitmaps can tell you whether the blocks are empty or formatted. Bitmaps do use additional space but this is less than 1% for most large objects.
The performance gain from using automatic segment management can be quite striking.
Permanent Tablespaces
Tablespaces can be either small tablespaces or big tablespaces
Tablespace commands
Datafile Commands
If you create tablespaces with non-standard block sizes you must set the DB_nK_CACHE_SIZE parameter, there are 5 nonstandard sizes 2k, 4k, 8k, 16k and 32k. The DB_CACHE_SIZE parameter sets the default block size for all new tablespace if the block size option is emitted.
Temporary tablespaces
Temporary tablespaces are used for order by, group by and create index. It is required when the system tablespace is locally managed. In oracle 10g you can now create temporary tablespace groups which means you can use multiple temporary tablespaces simultaneously.
The benefits of using a temporary tablespace group are
Temporary tablespace commands
See tables for more information on temporary tables.
Undo Tablespaces
Undo tablespaces are used to store original data after it has been changed, if a user decides to rollback a change the information in the undo tablespace is used to put back the data in its original state.
Undo tablespaces are used for the following
Creating
set default
create undo tablespace undotbs02 datafile ' c:\oracle\undo01.dbf' size 2G;
alter system set undo_tablespace='undotbs02';
See undo for more information.
Tablespace quotas
You can assign a user tablespace quota thus limiting to a certain amount of storage space within the tablespace. By default a user has none when the account is first created, see users for information on tablespace quotas.
Tablespace Alerts
The MMON daemon checks tablespace usage every 10 mins to see if any thresholds have been exceeded and raises any alerts. There are two types of alerts warning (low space warning) and critical (action should be taken immediately). Both thresholds can be changed via OEM or DBMS_SERVER_ALERT package.
Oracle Managed Files
Oracle can make file handling a lot easier by managing the oracle files itself, there are three parameters that can be set so that oracle will manage the data, temp, redo, archive and flash logs for you
Tablespace Logging
Tablespace logging can be overridden by logging specification at the table-level.