Temporary tablespace is used for sorting large tables. Every database should
have one temporary tablespace. A temporary tablespace is usually created at the
time of Database Creation. However you can also create temporary tablespace
To create temporary tablespace give the following command.
SQL> create temporary tablespace temp tempfile ‘/u01/oracle/data/ica_temp.dbf’ size 100M extent management local uniform size 5M;
The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The AUTOALLOCATE clause is not allowed for temporary tablespaces.
You can use the resize clause to increase or decrease the size of a temporary tablespace. The following statement resizes a temporary file:
SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
The following statement drops a temporary file and deletes the operating system file:
SQL> ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:
You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.
Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where 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.
The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.
You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.
For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:
CREATE TEMPORARY TABLESPACE ica_temp2 TEMPFILE '/u02/oracle/ica/ica_temp.dbf'
SIZE 50M TABLESPACE GROUP group1;
ALTER TABLESPACE ica_temp2 TABLESPACE GROUP group2;
Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
SQL>select * from dba_temp_files;
SQL>select * from v$tempfile;
To view information about free space in tempfiles
SQL>select * from V$TEMP_SPACE_HEADER;