Home / Oracle DBA / Overview of Table spaces

Overview of Table spaces

Overview of Table spaces

A table space is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a table space stores data in one or more data files or temp files. A database must have the SYSTEM and SYS AUX table spaces.
Permanent Table spaces
A permanent table space groups persistent schema objects. The segments for objects in the table space are stored physically in data files. Each database user is assigned a default permanent table space. A very small database may need only the default SYSTEM and SYSAUX table spaces. However, Oracle recommends that you create at least one table space to store user and application data. You can use table spaces to achieve the following goals:
Control disk space allocation for database data
Assign a quota (space allowance or limit) to a database user
Take individual table spaces online or offline without affecting the availability of the whole database
Perform backup and recovery of individual table spaces
Import or export application data by using the Oracle Data Pump utility
Create a transportable table space that you can copy or move from one database to another, even across platforms
The SYSTEM Table space
The SYSTEM table space is a necessary administrative table space included with the database when it is created. Oracle Database uses SYSTEM to manage the database. The SYSTEM table space includes the following information, all owned by the SYS user:
The data dictionary
Tables and views that contain administrative information about the database
Compiled stored objects such as triggers, procedures, and packages
The SYSTEM table space is managed as any other table space, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM table space.By default, Oracle Database sets all newly created user table spaces to be locally managed. In a database with a locally managed SYSTEM table space, you cannot create dictionary-managed table spaces (which are deprecated). However, if you execute the CREATE DATABASE statement manually and accept the defaults, then the SYSTEM table space is dictionary managed. You can migrate an existing dictionary-managed SYSTEM table space to a locally managed format.
The SYSAUX Table space
The SYSAUX table space is an auxiliary table space to the SYSTEM table space. The SYSAUX table space provides a centralized location for database metadata that does not reside in the SYSTEM table space. It reduces the number of table spaces created by default, both in the seed database and in user-defined databases. Several database components, including Oracle Enterprise Manager and Oracle Streams, use the SYSAUX table space as their default storage location. Therefore, the SYSAUX table space is created automatically during database creation or upgrade.
During normal database operation, the database does not allow the SYSAUX table space to be dropped or renamed. If the SYSAUX table space becomes unavailable, then core database functionality remains operational. The database features that use the SYSAUX table space could fail, or function with limited capability.
Note: Oracle strongly recommends that you use Database Configuration Assistant (DBCA) to create new databases so that all table spaces, including SYSTEM, are locally managed by default.
View More:
Archived Redo Log Files
Overview of the Online Redo Log
Overview of Control Files
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Your information will never be shared
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.