Types of Tablespaces


DBAs create tablespaces for the ease of control and maintenance over the information and structures within the Oracle database. There are two types of tablespaces in an Oracle database: SYSTEM and everything else.

The SYSTEM tablespace is created when you create the database. Its purpose is to hold the data dictionary tables, views, and stored program units. It also holds the SYSTEM undo segment. User data should never be stored in the SYSTEM tablespace although as yet, there is nothing to stop you from doing it.

The non-SYSTEM tablespaces are created by the DBA to enable more control and flexibility in database administration.

Non-SYSTEM tablespaces allow you to separate UNDO information, temporary information, application data, and application indexes. You can use tablespaces to separate data by backup requirements, by the characteristic of static over dynamic data, and as a means to control the amount of space allocated to user's objects.

We have established that the best way to maintain your database is to create additional tablespaces after the database has been created. The next few sections address the ways that we can create these additional tablespaces.

Undo Tablespace

Undo tablespaces are Oracle 9i's preferred manner of storing undo segments. Undo tablespaces cannot contain anything other than undo segments, and Oracle alone has direct control over them.

When creating an undo tablespace, you can specify only the DATAFILE clause and the EXTENT MANAGEMENT clause.

You can find more information on undo tablespaces and the management of undo data in Chapter 11, "Managing Undo Data."


The CREATE TABLESPACE statement for undo tablespaces follows:

 CREATE UNDO TABLESPACE mydb1_undo1 DATAFILE '/mydatabases/mydb1/mydb1_undo1.dbf' SIZE 100M; 

Temporary Tablespaces

Oracle uses temporary tablespaces for sort operations. Like undo tablespaces, temporary tablespaces cannot contain any other type of data (undo tablespaces can't contain anything but undo data; temporary tablespaces cannot contain any permanent objects). Oracle highly recommends that you create temporary tablespaces as locally managed.

Sort segments (also known as temporary segments) are used when a segment is shared by multiple sort operations. Temporary tablespaces provide performance improvements when you have multiple sorts going on at one time that are too big to fit easily into memory. The sort segment in the temporary tablespace is created at the time of the first large sort operation in the instance. This segment expands by allocating extents until the segment is equal to or greater than the total storage demands of all the active sorts running in the given instance.

The following is an example of the CREATE TABLESPACE statement for temporary tablespaces:

 CREATE TEMPORARY TABLESPACE mydb1_temp TEMPFILE '/mydatabases/mydb1/mydb1_temp01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M; 

Temporary tablespaces have several unique features that differ from normal tablespaces. These unique features are as follows:

  • Temporary tablespaces are always created with NOLOGGING mode enabled.

  • Temporary tablespaces can never be set to read-only.

  • Temporary tablespaces' data files (known as tempfiles) can never be renamed.

  • Temporary segments are even necessary for read-only databases (in fact, they may be even more important for read-only databases). The necessity of temporary tablespaces in a read-only database is due to the need for manipulating data being retrieved and for creating structures, albeit transient structures, to support user queries. Sorting needs to be done on retrieved data, groupings and aggregations, and computation, all of which require temporary segments. Many of these things, particularly temporary tables, could be created in a read/write database in the user's default tablespace. There is nowhere in a read-only database to do these other than a temporary tablespace.

  • Temporary tablespaces are not recovered in media recovery.

  • BACKUP CONTROLFILE does not generate information for temporary tablespaces' tempfiles.

  • CREATE CONTROLFILE cannot specify information concerning temporary tablespaces and their tempfiles.

  • To optimize the performance of sorts, the temporary tablespaces' UNIFORM EXTENT SIZE should be used and should be a multiple of SORT_AREA_SIZE.

Default Temporary Tablespaces

Default temporary tablespaces allow you, as DBA, to specify a database wide default temporary tablespace. Having a default temporary tablespace eliminates the chances that an errant sort will use the SYSTEM tablespace for storing sort data. You can create the default temporary tablespace either with the CREATE DATABASE command or with the ALTER DATABASE command. When you create it with the CREATE DATABASE command, the default temporary tablespace is always locally managed.

If the database has a default temporary tablespace, all users created without the TEMPORARY TABLESPACE clause will have the defined default as their temporary tablespace. Without having a default temporary tablespace, those users would be created having the SYSTEM tablespace assigned as their default temporary tablespace.

To set a tablespace as the default temporary tablespace after the database has been created, you can run the following command:

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE mydb_temp1; 

After the tablespace has been made the default temporary tablespace, users who do not have an explicitly assigned temporary tablespace will have that as theirs. The DEFAULT TEMPORARY TABLESPACE can be reassigned by use of the ALTER DATABASE command. After the ALTER DATABASE command has been successfully run, all users who have the DEFAULT TEMPORARY TABLESPACE assigned to them will have the new default assigned to them.

It is important to note that, if you create a temporary tablespace in this manner, there is a good chance that Oracle will understand that the tablespace is to be sized at the SIZE parameter. However, at the operating system level, it is not always created at this size but is created sparsely. If you are creating files in different file systems, you may allocate more space in a file system than actually exists in the file system. This can cause issues when the database tries to fill out those tempfiles later, if you don't take the additional space requirements into account.


But great as it is to be able to use default temporary tablespaces, there are restrictions on them. Those restrictions are as follows:

  • After you have defined a default temporary tablespace, it cannot be dropped until you have defined a new temporary tablespace.

  • It cannot be taken offline.

  • It cannot be made read-only.

  • You cannot alter the default temporary tablespace to make it a permanent tablespace.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net