Overview of Oracle Temporary Tablespace (Creating/Resizing)
6 min read
Temporary Tablespaces
A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session.
Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace.
By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. The performance of temporary tablespaces is extremely critical when your application uses sort- and hash-intensive queries, which need to store transient data in the temporary tablespace.
Temporary Tablespace Groups
Large transactions can sometimes run out of temporary space. Large sort jobs, especially those involving tables with many partitions, lead to heavy use of the temporary tablespaces, thus potentially leading to a performance issue.
Oracle Database 10g introduced the concept of a temporary tablespace group, which allows a user to utilize multiple temporary tablespaces simultaneously in different sessions.
Here are some of the main characteristics of a temporary tablespace group:
1. A temporary tablespace group must consist of at least one tablespace. There is no explicit maximum number of tablespaces.
2. If you delete all members from a temporary tablespace group, the group is automatically deleted as well.
3. A temporary tablespace group has the same namespace as the temporary tablespaces that are part of the group.
4. The name of a temporary tablespace cannot be the same as the name of any tablespace group.
5. When you assign a temporary tablespace to a user, you can use the temporary tablespace group name instead of the actual temporary tablespace name. You can also use the temporary tablespace group name when you assign the default temporary tablespace for the database.
Benefits of Temporary Tablespace Groups
1. SQL queries are less likely to run out of sort space because the query can now simultaneously use several temporary tablespaces for sorting.
2. You can specify multiple default temporary tablespaces at the database level.
3. Parallel execution servers in a parallel operation will efficiently utilize multiple temporary tablespaces.
4. A single user can simultaneously use multiple temporary tablespaces in different sessions.
Creating a Temporary Tablespace Group
When you assign the first temporary tablespace to a tablespace group, you automatically create the temporary tablespace group. To create a tablespace group, simply specify the TABLESPACE GROUP clause in the CREATE TABLESPACE statement, as shown here:
The preceding SQL statement will create a new temporary tablespace, temp01, along with the new tablespace group named tmpgrp1. Oracle creates the new tablespace group because the key clause TABLESPACE GROUP was used while creating the new temporary tablespace.
You can also create a temporary tablespace group by specifying the same TABLESPACE GROUP clause in an ALTER TABLESPACE command, as shown here:
SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp1;
Adding a Tablespace to a Temporary Tablespace Group
As shown in the preceding section, you can add a temporary tablespace to a group by using the ALTER TABLESPACE command. You can also change which group a temporary tablespace belongs to by using the ALTER TABLESPACE command. For example, you can specify that the tablespace temp02 belongs to the tmpgrp2 group by issuing the following statement:
SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp2;
Setting a Group as the Default Temporary Tablespace for the Database
You can use a temporary tablespace group as your default temporary tablespace for the database. If you issue the following statement, all users without a default tablespace can use any temporary tablespace in the tmpgrp1 group as their default temporary tablespaces:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmpgrp1;
Viewing Temporary Tablespace Group Information
You can use the new DBA_TABLESPACE_GROUPS data dictionary view to query the temporary tablespace groups in your database. Here is a simple query on the view that shows the names of all tablespace groups:
SQL> SELECT group_name, tablespace_name FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
---------- ---------------
TMPGRP1 TEMP01
or,
SQL> SELECT username, temporary_tablespace FROM dba_users;
USERNAME TEMPORARY_TABLESPACE
-------- ---------------------
SYS TEMP
SYSTEM TEMP
TEST TMPGRP2
Viewing Space Usage for Temporary Tablespaces
The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.
12c database with Multitenant- Things to make a note of:
1. There is one default temporary tablespace at the entire CDB level and you can create multiple temporary tablespaces at the CDB level and like traditional database, only one can be default temp tablespace at the CDB level. Users can be explicitly assigned some temp tablespaces at PDB level.
2. At the PDB level also, we can have the same structure like traditional one with multiple temp tablespaces and one default temp tablespaces. Users can be explicitly assigned some temp tablespaces at PDB level.
3. If a user has temp tablespace assigned explicitly at the CDB and PDB level, then the temp tablespace assigned to it depends on the container(PDB or CDB) in which it is present currently.
4. If a user is present in PDB but is not assigned any temp tablespace explicitly and the PDB also doesn't have any default temp tablespace, then the default temp tablespace at the CDB level is assigned to that user(where as in traditional one we have system tablespace assigned).
The physical files that comprise a temporary tablespace are called tempfiles, as opposed to datafiles and information on these files are available in dba_temp_files or v$tempfile:
SQL> select * from dba_temp_files order by tablespace_name;
SQL> select * from v$tempfile;
column name format a55
column info format a65
select t.TABLESPACE_NAME,t.FILE_NAME, to_char(t.BYTES,'999,999,999,999')||' Bytes, '||t.STATUS||' '||d.STATUS||' '|| d.CONTENTS Info from dba_temp_files t, dba_tablespaces d where t.TABLESPACE_NAME = d.TABLESPACE_NAME ;
TO RESIZE THE TEMPFILE IN TEMP TABLESPACE
alter database tempfile '/u01/app/oracle/oradata/asrblg/temp01.dbf' resize 250M
alter database tempfile '/u01/app/oracle/oradata/asrblg/temp02.dbf' autoextend on maxsize 1800M;
alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/asrblg/temp03.dbf' size 1800m reuse;
How to find Current Size of Temporary Tablespace
SQL> select sum(bytes)/1024/1024/1024 gb from dba_temp_files where tablespace_name = ‘TEMP’;
To find which sessions are using temp space
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
To find the objects held in the TEMP tablespace.
select srt.tablespace, srt.segfile#,srt.segblk#, srt.blocks,a.sid, a.serial#, a.username,a.osuser, a.status
from v$session a,v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#,srt.blocks;
or,
SELECT S.sid || ‘,’ || S.serial# sid_serial,S.username, T.blocks * 8192 / 1024 / 1024 /1024 gb_used,T.tablespace, Q.sql_fulltext, q.sql_id FROM v$sort_usage T join v$session S on T.session_addr = S.saddr left join v$sqlarea Q on T.sqladdr = Q.address ORDER BY mb_used desc, sid_serial;
