Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. Numerous SQL operations that require disk sorting such as: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc. Thus assigning a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.
SQL>CREATE USER HRMS DEFAULT TABLESPACE Main_DB TEMPORARY TABLESPACE Temp;
SQL> ALTER USER HRMS TEMPORARY TABLESPACE temp;
TEMPFILES?
Unlike normal data files, TEMPFILE are not fully initialized. When you create a TEMPFILE, Oracle only writes to the header and last block of the file. TEMPFILEs are not recorded in the database’s control file so that one can simply re-create it after accidental delete. Temp files cannot contain permanent objects and therefore doesn’t need to be backed up.
One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database.
SQL> ALTER DATABASE TEMPFILE ‘C:\oracle1\oradata\temp03.dbf’ DROP INCLUDING DATAFILES;
If you remove all tempfiles from a temporary tablespace, you may get error: ORA-25153: Temporary Tablespace is Empty. To Add Tempfile:
SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘C:\oracle1\oradata\temp03.dbf’ SIZE 100M;
Except for adding a tempfile you cannot use the other ALTER TABLESPACE statement for a locally managed temporary tablespace such as operations like rename, set to read only, recover, etc. will fail with tempfile.
How does one create Temporary Tablespaces?
Oracle 9i example:
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/oradata/mytemp_01.tmp’ SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter. For Example: To create temporary tablespace.
SQL> CREATE TEMPORARY TABLESPACE temp;
Default Temporary Tablespaces:
In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
The default Default Temporary Tablespace is SYSTEM. Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users. The following restrictions apply to default temporary tablespaces:
– The Default Temporary Tablespace must be of type TEMPORARY
– The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
– The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.
To check the default temporary tablespace for a database:
SQL>SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;
All new users that are not explicitly assigned a TEMP TABLESPACE will get the Default Temp Tablespace as its TABLESPACE. Also, when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the Default Temporary Tablespace for the database.
Other performance Considerations for Temp Tablespaces:
– Always use temporary tablespaces instead of permanent content tablespaces for sorting operation. By using nologging and large sort segment to reduce recursive SQL and ST space management enqueue contention.
– Always create your temporary tablespaces as locally managed instead of dictionary managed. Thus use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space.
– Always use TEMPFILEs instead of DATAFILEs will reduce backup and recovery time as well as Prevent allocating sort space in SYSTEM tablepace.
– Stripe your Temp tablespaces over multiple disks to minimize disk contention and to speed-up sorting operations.
Monitoring TEMP Tablespace:
Use V$TEMPFILE and DBA_TEMP_FILES to check the Tempfile.
Monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
Use V$TEMP_SPACE_HEADER to check free space of temporary tablespace.
