Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and User friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

Step 6 - Tablespaces

Learn Oracle - Tablespaces

A database consists of one or more tablespaces. A tablespace is a logical structure, or container, created by and known only to the Oracle database server in which database objects are stored. A tablespace consists of one or more datafiles or tempfiles.

There are various types of tablespaces, including undo tablespaces, temporary tablespaces, and permanent tablespaces.

A database running in automatic undo management mode transparently creates and manages undo segments. Oracle DBA can create an undo tablespace and determine the maximum retention time for undo data kept in that tablespace.

Temporary tablespaces improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether. Temporary tablespaces are the most efficient tablespaces for disk sorts. Space management (extent allocation and deallocation) is locally managed. After temporary tablespaces, tablespaces of type TEMPORARY are the next best tablespaces to use for sort operations. Space management is dictionary managed. Permanent tablespaces (which are not of type TEMPORARY) are least efficient for performance of disk sorts.

Temporary and permanent tablespaces can be assigned at the user level. Oracle DBA can create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.

Tablespaces Page

Click Tablespaces. The Tablespaces page provides high level information about tablespace structure and space usage. Oracle DBA can see how much space is allocated for the tablespace and what portion of it is used. Select a tablespace and click View to drill down to see the underlying datafile attributes.

Some Tablespaces in the Database

Tablespace Description


This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that prior to Oracle Database 10g used the SYSTEM tablespace or their own tablespaces now use the SYSAUX tablespace. This reduces the load on the SYSTEM tablespace and reduces maintenance because there are fewer tablespaces to monitor and maintain. Every Oracle Database 10g or higher level database must have a SYSAUX tablespace. Components that use this as their default tablespace during installation include Automatic Workload Repository, Oracle Streams, Oracle Text, and Enterprise Manager Repository. For more information, see Oracle Database Administrator's Guide


The SYSTEM tablespace is always created at database creation. Oracle uses it to manage the database. It contains the data dictionary, which is the central set of tables and views used as a read-only reference describing a particular database. It also contains various tables and views that contain administrative information about the database. These are all contained in the SYS schema, and can only be accessed by user SYS, or other administrative users with the required privilege.


This tablespace stores temporary data generated when processing SQL statements. It would, for example, be used for sort work space. Every database should have a temporary tablespace that is assigned to users as their temporary tablespace. In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. This means that if no temporary tablespace is specified when the user account is created, then Oracle assigns this tablespace to the user.


This is the undo tablespace used by the database server to store undo information. See "Managing Undo for Your Database" to understand Oracle’s use of the undo tablespace. Every database must have an undo tablespace that can be created at database creation.

EXAMPLE This tablespace contains the sample schemas that Oracle includes with the database. The sample schemas provide a common platform for examples. Oracle documentation and educational materials contain examples based on the sample schemas.

Temporary Tablespace Groups

Temporary tablespace groups lets users consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused when one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

A tablespace group name can appear where a tablespace name would appear when assigning a default temporary tablespace for the database or assigning a temporary tablespace for a user. Click the Temporary Tablespace Groups link. Oracle DBA see the temporary tablespace groups that are defined for your database. Initially, there are none.
Oracle Tutorial - BLOGS

STEP 6 - Next Topics
This Step discusses management of the database's storage structure. Click on Oracle tuorial links below:
The Control FileControl file
Redo Log FilesRedo log file
Archive Log FilesArchive log file
Initialization Parameter File
Password File
Backup Files
Viewing Tablespaces in your Database
Creating a tablespace
Modifying a Tablespace
Dropping a Tablespace
Managing Undo for Your Database
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info