Temporary Tablespaces

What are Temporary Tablespaces:

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.

Considerations:

Some performance considerations for temporary tablespaces:

– Always use temporary tablespaces instead of permanent content tablespaces for sorting (no logging and uses one large sort segment to reduce recursive SQL and ST space management enqueue contention).
– Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed (Use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space).
– Always use TEMPFILEs instead of DATAFILEs (reduce backup and recovery time + other advantages as described above)
– Stripe your temporary tablespaces over multiple disks to alleviate possible disk contention and to speed-up sorting operations (user processes can read/write to it directly).

 

Dropping / Recreating Temporary Tablespace

 Before dropping a temporary tables make sure that no users are connected.

 Also you cannot drop a default temp tablespace. You need to create another temp tablespace as default and drop the earlier.

 Below are the steps

 SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/the/full/path/to/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

 

Dropping a temp file

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

 

Altering a temp file

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' 
                                           DROP INCLUDING DATAFILES;
(For locally managed temp tbspaces)
SQL> alter tablespace <your_temp_ts> shrink space keep 128M; 

 

If users are currently accessing the tempfile you are attempting to drop, you may receive the following error:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/BHARATDB/temp01.dbf' 
                                           DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' 
                                           DROP INCLUDING DATAFILES;
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

If this happens, you should attempt to drop the tempfile 
again so the operation is successful:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/BHARATDB/temp01.dbf' 
                                            DROP INCLUDING DATAFILES;
 Database altered.