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:
