The Logical Oracle Database


Tablespaces

Tablespaces are the Oracle database internal mapping to or database accessible representation of the datafiles and the contents of those datafiles. Databases are divided into logical storage sets called tablespaces. Tablespaces are in turn divided into logical sections called segments. Segments are divided into physical extents.

Locally and Dictionary Managed Tablespaces

In previous version of Oracle tablespaces could only be dicionary managed. This means that tablespace space utilisation tracking was stored in data dictionary SQL tables. Oracle8i allows creation of locally managed tablespaces. Locally managed tablespaces use bitmaps (not SQL tables) to track used and free space (PCTFREE and PCTUSED). Dictionary managed tablespaces are still the default but locally managed tablespaces are now recommended. All extents are tracked by bitmaps in locally managed tablespaces. Locally managed tablespaces have improved concurrency, performance, simplified space allocation (automatically managed) and reduction in the reliance on users. Thus locally managed tablespaces are more simple and efficient. The DBMS_SPACE_ADMIN package allows for locally administered tablespace maintenance. Note that the SYSTEM tablespace must be data dictionary managed. Locally managed tablespaces are ideally suited for temporary tablespaces.

Minimum Required Tablespaces

Two additional tablespaces should also be included. Both of these tablespaces apply to a single application. Thus one should create both a data and an index tablespace for each application using the Oracle instance. Note that the designations of these tablespaces is not absolutely required but is advisable. In fact, the tablespaces can be divided up even further.

Note that the datafile required for instance startup is the SYSTEM tablespace.

The Oracle Database File Layout

All About Tablespaces

The SYSTEM tablespace is required by an Oracle database instance. All items other than the data dictionary should be separated from the SYSTEM tablespace. Database files can be divided up into different tablespaces and onto different disks. It is sensible to divide up tablespaces according to their different functions. Thus it is also sensible to divide up those different tablespaces onto different disks. For instance, tables and indexes can be put onto different disks. This can reduce I/O disk contention significantly since there are multiple physical access paths to data in tables and indexes.

Five Good Reasons to Separate Tablespaces

There are a number of very good reasons why tablespaces should be separated in terms of their contents and the disks those tablespaces are placed on. Tablespaces should be reserved for specific uses. Separate different types of uses such as high-update activity, read-only activity or temporary segment storage.

  1. Independant non-SYSTEM tablespaces are easier to maintain. Any tablespace other than the SYSTEM tablespace can be brought off-line or backed-up and recovered without shutting down the Oracle instance. Also a non-SYSTEM tablespace can be deleted or built new without shutting down the Oracle instance.
  2. Separate user tablespaces helps to control the amount of database space allocated to each user. Thus legitimate users will receive adequate space and excessive use of disk space can be prevented by a single user.
  3. Separate tablespaces will help to reduce free space disk fragmentation. Creating and dropping objects causes free space to be fragmented. The more creating and dropping of objects that is done creates more and more fragmentation of files. The result is that files will not be contiguous and will be divided up over different areas of a disk. This will lead to slower seek times.
  4. It is a good idea to put more volatile database objects into their own tablespaces. This would separate database objects subject to fragmentation from database objects which are static and remain contiguous. Data dictionary tables in the SYSTEM tablespace of a production database are generally static. Temporary work space segments change constantly. Thus allowing temporary work areas to remain in the SYSTEM tablespace could slow down database access significantly.
  5. Separate tablespaces can simplify distribution of data. Database access is generally improved if data is distributed across a number of different disks since each disk can read or write data concurrently.

Database Objects

Database objects are logical internal Oracle mappings to the contents of the datafiles and the meta-data. In fact database objects are the meta-data. They are the logical mappings of the data about the data. Types of database objects are listed as below.

Rollback Segments

Rollback segments allow maintenance of consistency and integrity of data amongst multiple users. When a rollback is executed changes to the database can be undone using the data before images stored in the rollback segments. When a database change is made the changes are written to both the database buffer cache, the rollback segments and the redo log buffer. When a commit is performed the rollback segment entries for the transaction are removed. When a rollback is performed the rollback segment is used to undo database changes in the database cache buffer and the redo logs by placing the before change images back into the database buffer cache and the redo log buffer. This is the most efficient method since rollback operations are much more infrequent than commit statements.