Tablespaces

Tablespace types and overview:

Before I start with Tableespace, We must have fair idea of data. Tablespace is the logical memory to hold the information about physical memory.
Data can be classified into many types. While we install database , we have data related to system so, those data are called System data and can be taken into consideration for System tablespace.

While we do any operation like join, orderby etc. , for this process to succeed we need some temporary space to happen this process and temp space is used and considered as part of temp tablespace.

Any operation like alter, create, we need to hold these information for flashback and recovery purpose. So these data are stored into undo section and considered into Undo tablespace. By default undo retention is 900 second. We can make it more if we need the data for more that this duration.

Sysaux tablespace is the auxiliary tablespace of System tablespace. While we make some additional feature then the system related data will come into the Sysaux tablespace.

TEMPORARY TABLESPACE:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

Note: A temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:

SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

How does one create Temporary Tablespaces?

Oracle provides various ways of creating TEMPORARY tablespaces (mainly to provide backward compatibility). One should use the most recent method available:

- Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...;
- Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY;
- Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;

SQL> CREATE TEMPORARY TABLESPACE temp
      TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:




UNDO TBALESPACE:

The Undo tablespace is used for several features: ROLLBACK, READ CONSISTENCY and FLASHBACK technology.

Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was purged. In Oracle 9i this method of manual undo management is still available in addition to a new automatic method which frees DBAs from routine undo management tasks and tuning. In addition it allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.

Monitoring
Undo information can be queried using the following views.

• V$UNDOSTAT
• V$ROLLSTAT
• V$TRANSACTION
• DBA_UNDO_EXTENTS

SYSAUX TABLESPACE:

The SYSAUX tablespace provides storage of non-sys-related tables and indexes that traditionally were placed in the SYSTEM tablespace. For example, the tables and indexes that were previously owned by the system user can now be specified for a SYSAUX tablespace. Unfortunately, Oracle still places the SCOTT schema and the other demonstration schemas in the SYSTEM tablespace. Go figure.

The SYSAUX tablespace is specified with the CREATE DATABASE command.


During any update of a database to Oracle Database 10g, a SYSAUX tablespace must be created or the upgrade will fail. The SYSAUX tablespace has the same security profile as the SYSTEM tablespace. However, loss of the SYSAUX tablespace will not result in a database crash, only the functional loss of the schemas it contains.

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

Thus, while it's critical to have the SYSTEM tablespace never ever fail, one doesn't need to be nearly as paranoid for SYSAUX, this allows the designers to keep the size of SYSTEM down, while allowing third-party auxiliary features to be part of the "core" database.
  
SYSAUX Tablespace (Auxillary to the SYSTEM tablespace).

Oracle 10g has introduced new tablespace called SYSAUX tablespace. The purpose of SYSAUX tablespace is to reduce contention on SYSTEM tablespace by placing non-essential database metadata (i.e non system related tables and indexes) which were traditionally stored in SYSTEM tablespace.

SYSAUX tablespace is mandatory in Oracle 10g database whether you create new database or migrate from older versions. The size of SYSAUX tablespace is dependent on size of database components that you'll store in however Oracle recommends the size of tablespace to be at least 250MB. While creating database using DBCA SYSAUX tablespace is automatically created
  
Altering SYSAUX tablespace requires  SYSDBA privilege. The syntax is similar to the other tablespace with some restrictions. SYSAUX tablespace can't be dropped, can't be renamed during normal database operation and can't transport SYSAUX tablespace.

ALTER TABLESPACE SYSAUX
  ADD DATAFILE ''/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 500M

Oracle requires that the SYSAUX tablespace has the following attributes:

• Online
• Permanent
• Read write
• Extent management local
• Segment space management auto