What is the difference between system and sysaux tablespace in oracle

Other tablespaces remain online, and the information in them is still available for use. However, special circumstances can occur when tablespaces are taken offline.

For example, if two tablespaces are used to separate table data from index data, the following is true:. If the tablespace containing the indexes is offline, then queries can still access table data because queries do not require an index to access the table data.

If the tablespace containing the tables is offline, then the table data in the database is not accessible because the tables are required to access the data. If Oracle has enough information in the online tablespaces to run a statement, it does so. If it needs data in an offline tablespace, then it causes the statement to fail. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces cannot be modified.

After updating the tablespace, you can then reset it to be read only. Also, if you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified. You can manage space for sort operations more efficiently by designating one or more temporary tablespaces exclusively for sorts. Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space.

A single SQL operation can use more than one temporary tablespace for sorting. For example, you can create indexes on very large tables, and the sort operation during index creation can be distributed across multiple tablespaces. All operations that use sorts, including joins, index builds, ordering, computing aggregates GROUP BY , and collecting optimizer statistics, benefit from temporary tablespaces.

The performance gains are significant with Real Application Clusters. One or more temporary tablespaces can be used only for sort segments. A temporary tablespace is not the same as a tablespace that a user designates for temporary segments, which can be any tablespace available to the user.

No permanent schema objects can reside in a temporary tablespace. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for every instance that performs a sort operation in a given tablespace.

Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment expands by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance.

Oracle Database Performance Tuning Guide for information about setting up temporary tablespaces for sorts and hash joins. A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even across different platforms. You can clone a tablespace and plug it into another database, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Oracle database, moving the tablespace between databases.

When you transport tablespaces you can also move index data, so you do not have to rebuild the indexes after importing or loading the table data. You can transport tablespaces across platforms. Many, but not all, platforms are supported for cross-platform tablespace transport. This can be used for the following:. Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle on a different platform.

Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller platforms. A tablesp ace repository is a collection of tablespace sets. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases.

Different tablespace sets may be stored in a tablespace repository, and different versions of a particular tablespace set also may be stored. A version of a tablespace set in a tablespace repository consists of the following files:.

Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat files, such as the operating system copying facility, ftp, or publishing on CDs.

These files have identical on disk formats for file header blocks, which are used for file identification and verification. Oracle Database Administrator's Guide for details about how to move or copy tablespaces to another database, including details about transporting tablespaces across platforms. Oracle Streams Concepts and Administration for more information on ways to copy or transport files.

A t ablespace in an Oracle database consists of one or more physical datafiles. A datafile can be associated with only one tablespace and only one database. Oracle creates a datafile for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header. When a datafile is created, the operating system under which Oracle runs is responsible for clearing old information and authorizations from a file before allocating it to Oracle. If the file is large, this process can take a significant amount of time.

When a datafile is first created, the allocated disk space is formatted but does not contain any user data. However, Oracle reserves the space to hold the data for future segments of the associated tablespace—it is used exclusively by Oracle. As the data grows in a tablespace, Oracle uses the free space in the associated datafiles to allocate extents for the segment. The data associated with schema objects in a tablespace is physically stored in one or more of the datafiles that constitute the tablespace.

Note that a schema object does not correspond to a specific datafile; rather, a datafile is a repository for the data of any schema object within a specific tablespace. Oracle allocates space for the data associated with a schema object in one or more datafiles of a tablespace. Therefore, a schema object can span one or more datafiles. Unless table striping is used where data is spread across more than one disk , the database administrator and end users cannot control which datafile stores a schema object.

You can alter the size of a datafile after its creation or you can specify that a datafile should dynamically grow as schema objects in the tablespace grow. This functionality enables you to have fewer datafiles for each tablespace and can simplify administration of datafiles. All of the datafiles of a tablespace are taken offline or brought online as a unit when you take the tablespace offline or bring it online, respectively. You can take individual datafiles offline. However, this is usually done only during some database recovery procedures.

Locally managed temporary tablespaces have temporary datafiles tempfile s , which are similar to ordinary datafiles, with the following exceptions:. When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems for example, UNIX disk blocks are allocated not at file creation or resizing, but before the blocks are accessed. The database control file is a small binary file necessary for the database to start and operate successfully.

A control file is updated continuously by Oracle during database use, so it must be available for writing whenever the database is open. If for some reason the control file is not accessible, then the database cannot function properly.

A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation. Control file information can be modified only by Oracle; no database administrator or user can edit a control file. The names and locations of associated datafiles and redo log files. The database name and timestamp originate at database creation. Each time that a datafile or a redo log file is added to, renamed in, or dropped from the database, the control file is updated to reflect this physical structure change.

These changes are recorded so that:. Control files also record information about checkpoints. Every three seconds, the checkpoint process CKPT records information in the control file about the checkpoint position in the redo log. This information is used during database recovery to tell Oracle that all redo entries recorded before this point in the redo log group are not necessary for database recovery; they were already written to the datafiles.

As with redo log files, Oracle enables multiple, identical control files to be open concurrently and written for the same database.

By storing multiple control files for a single database on different disks, you can safeguard against a single point of failure with respect to control files. If a single disk that contained a control file crashes, then the current instance fails when Oracle attempts to access the damaged control file. However, when other copies of the current control file are available on different disks, an instance can be restarted without the need for database recovery. If all control files of a database are permanently lost during operation, then the instance is aborted and media recovery is required.

Media recovery is not straightforward if an older backup of a control file must be used because a current copy is not available. It is strongly recommended that you adhere to the following:. This chapter contains the following topics: Introduction to Tablespaces, Datafiles, and Control Files Overview of Tablespaces Overview of Datafiles Overview of Control Files Introduction to Tablespaces, Datafiles, and Control Files Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

Oracle-Managed Files Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures: Tablespaces Redo log files Control files Through initialization parameters, you specify the file system directory to be used for a particular type of file.

Allocate More Space for a Database The size of a tablespace is the size of the datafiles that constitute the tablespace. You can enlarge a database in three ways: Add a datafile to a tablespace Add a new tablespace Increase the size of a datafile When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace.

See Also: Oracle Database Administrator's Guide for more information about increasing the amount of space in your database. Overview of Tablespaces A d atabase is divided into one or more logical storage units called tablespaces. Bigfile Tablespaces Ora cle lets you create bigfile tablespaces. Benefits of Bigfile Tablespaces Bigfile table spaces can significantly increase the storage capacity of an Oracle database.

They simplify database management by providing datafile transparency. Considerations with Bigfile Tablespaces Bigfile t ablespaces are intended to be used with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.

Every database using Oracle Database 10 g release 1 For more information, see Oracle Database Administrator's Guide. This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace would be used for query sorting. 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.

If no temporary tablespace is specified when a user account is created, then Oracle Database assigns this tablespace to the user. This is the undo tablespace used by the database to store undo information. See " Managing Undo Data " to understand how an Oracle database uses the undo tablespace. Every database must have an undo tablespace. This tablespace is used to store permanent user objects and data. Similar to the TEMP tablespace, every database should have a tablespace for permanent user data that is assigned to users.

You can create new tablespaces to support your user and application data requirements. Part 1b can help you get a handle on the functionality that is consuming the space. Where Part 1a may say that sys is the biggest holder of space, that schema will contain the AWR repository tables, Advisor findings and much more. Part 1b helps understand how the sys schema is really utilizing that space.

Schema SYS occupies 1, Schema XDB occupies Only the first few lines of Part 1b were shown for brevity. It is easy to see that the AWR repository is accounting for 1, If the sysaux tablespace seems to be growing rather large, the awrinfo. Part 2 of the report helps you plan AWR space usage if the default interval or retention period is changed. This information is also useful when expanding the number of Oracle RAC nodes. Estimates based on 24 snaps in past 24 hours:.

From the output above, it is clear that each snapshot is taking 4, kilobytes of space. Changing the default collection interval to 30 minutes would double the total size from 1, The database administrator can expand the sysaux tablespace accordingly if the data will be collected more often than the default.

The information above also shows that each instance is holding Part 3 of the script output breaks down space usage of AWR even more. If Part 2 is showing that AWR repository data is consuming large amounts of space, then Part 3 can be used to drill down into the problem area with more accuracy.