Monitoring Oracle Temp Tablespace Usage: Tips And Tricks

Monitoring temporary tablespace usage in Oracle is crucial for database management. Temporary tablespaces are used for sorting purposes and are shared among different users. High usage can lead to errors, such as the ORA-1652: unable to extend temp segment. To monitor usage, specific queries can be used to identify the tablespace name, used and free space, and the users and SQL statements contributing to high usage. These queries provide insights into the sessions and programs utilising the temporary tablespace, helping to optimise performance and troubleshoot issues related to temporary tablespace utilisation.

Identify temporary table space names

To monitor the usage of a temporary tablespace in Oracle, the first step is to identify the temporary tablespace names. This can be done using the following SQL query:

> SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

This query will return the names of all the temporary tablespaces in the Oracle database. With these tablespace names, you can then proceed to the next steps of monitoring their usage.

Another way to identify the temporary tablespace names is by querying the DBA_TEMP_FILES view. This view provides information on all temporary files associated with the database, including their names, sizes, and free space. Here is the SQL query for this method:

> SELECT tablespace_name, file_name, bytes/1024/1024 MB, status FROM dba_temp_files;

This query will return the name of each temporary tablespace, along with additional details such as the file name, size in megabytes, and status.

Additionally, you can also find the default temporary tablespace name, which is typically "TEMP," by executing the following statement:

> SELECT property_name, property_value FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

By following these steps, you can easily identify the temporary tablespace names in your Oracle database, which is the first step in effectively monitoring their usage.

Find out used and free space

To find out the used and free space in a temporary tablespace, you can use the following SQL queries.

Firstly, you need to identify the temporary tablespace names:

Sql

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

Next, you can use the below SQL to identify the used and free space in the temporary tablespace:

Sql

SELECT * FROM (SELECT a.tablespace_name, SUM(a.bytes/1024/1024) allocated_mb FROM dba_temp_files a WHERE a.tablespace_name = 'TEMP' GROUP BY a.tablespace_name ) x, (SELECT SUM(b.bytes_used/1024/1024) used_mb, SUM(b.bytes_free /1024/1024) free_mb FROM v$temp_space_header b WHERE b.tablespace_name = 'TEMP' GROUP BY b.tablespace_name) ;

The above query will return the allocated, used, and free space in MB for the temporary tablespace.

You can also use the following query to check the percentage utilisation of the temp tablespace:

Sql

SELECT (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

This will return the percentage of the temporary tablespace that is currently in use.

Additionally, you can use the following query to find out the total size, used size, and free size of the temp tablespace:

Sql

SELECT A.tablespace_name tablespace, D.gb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_used, D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.gb_total;

Find out which sessions are using space

To find out which sessions are using space in the temporary tablespace, you can use the following SQL query:

Sql

SELECT s.sid || ',' || s.serial# sid_serial,

S.username,

S.osuser,

P.spid,

S.module,

S.program,

SUM (o.blocks) * t.block_size / 1024 / 1024 mb_used,

O.tablespace,

COUNT(*) sorts

FROM v$sort_usage o, v$session s, dba_tablespaces t, v$process p

WHERE o.session_addr = s.saddr AND s.paddr = p.addr AND o.tablespace = t.tablespace_name

GROUP BY s.sid, s.serial#, s.username, s.osuser, p.spid, s.module, s.program, t.block_size, o.tablespace

ORDER BY sid_serial;

This query will return a list of sessions that are currently using space in the temporary tablespace, along with details such as the username, OS user, and the amount of space used.

Another query that can be used to find the sessions using the most space in the temporary tablespace is:

Sql

Cursor bigtemp_sids is select * from (

Select s.sid, s.status, s.sql_hash_value sesshash, u.SQLHASH sorthash, s.username, u.tablespace, sum(u.blocks*p.value/1024/1024) mbused, sum(u.extents) noexts, nvl(s.module,s.program) proginfo, floor(last_call_et/3600)||':'|| floor(mod(last_call_et,3600)/60)||':'|| mod(mod(last_call_et,3600),60) lastcallet from v$sort_usage u, v$session s, v$parameter p where u.session_addr = s.saddr and p.name = 'db_block_size' group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace, nvl(s.module,s.program), floor(last_call_et/3600)||':'|| floor(mod(last_call_et,3600)/60)||':'|| mod(mod(last_call_et,3600),60) order by 7 desc,3) where rownum < 11;

This query will return the top 10 sessions with the highest temporary tablespace usage, including details such as the session ID, username, and the amount of space used.

Additionally, if you are using Oracle RAC with multiple instances, you will need to modify the query as follows:

Sql

SELECT b.TABLESPACE, a.username, a.osuser, a.program, a.status, 'ALTER SYSTEM KILL SESSION '''||a.SID||','||a.SERIAL#||',@'||a.inst_ID||''' IMMEDIATE;' FROM gv$session a, gv$sort_usage b, gv$process c, gv$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr -- AND b.TABLESPACE='TEMP' ORDER BY a.inst_ID, b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;

This query will return the sessions that are using space in the temporary tablespace for each instance in an Oracle RAC environment.

Find current sessions using temp tablespace

To find the current sessions using temp tablespace in Oracle, you can use the following SQL queries:

Option 1:

Sql

SELECT * FROM DBA_TEMP_FREE_SPACE;

This query will return the tablespace name, tablespace size, allocated space, free space, shared status, and instance ID. This query only works for Oracle 12c and above.

Option 2:

Sql

Set lines 200

Select TABLESPACE_NAME, sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024) from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME;

This query will return the tablespace name, the sum of bytes used, and the sum of bytes free in the temporary tablespace.

Option 3:

Sql

SELECT (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

This query will return the percentage utilisation of the temp tablespace.

Option 4:

Sql

SELECT s.sid || ',' || s.serial# sid_serial, s.username, s.osuser, p.spid, s.module, s.program, SUM (o.blocks) * t.block_size / 1024 / 1024 mb_used, o.tablespace, COUNT(*) sorts FROM v$sort_usage o, v$session s, dba_tablespaces t, v$process p WHERE o.session_addr = s.saddr AND s.paddr = p.addr AND o.tablespace = t.tablespace_name GROUP BY s.sid, s.serial#, s.username, s.osuser, p.spid, s.module, s.program, t.block_size, o.tablespace ORDER BY sid_serial;

This query will return the session ID, serial number, username, OS user, process ID, module, program, total MB used, tablespace, and the number of sorts.

Option 5:

Sql

SELECT sysdate, a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c, (select block_size from dba_tablespaces where tablespace_name='TEMP') d WHERE b.tablespace = 'TEMP' and a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND (b.blocks*d.block_size)/1048576 > 1024 ORDER BY b.tablespace, 6 desc;

This query will identify the current users who are using the TEMP tablespace or utilising the TEMP segments. It will return the current date, username, session ID, serial number, OS user, MB used, and the SQL text.

Find out percentage utilisation of temp tablespace

To monitor temp tablespace usage in Oracle, you can use a variety of queries to identify usage and utilisation percentages.

First, you need to identify the temporary table space names:

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

Next, you can use the following SQL to identify the used and free space in the temporary tablespace:

SELECT * FROM (SELECT a.tablespace_name, SUM(a.bytes/1024/1024) allocated_mb FROM dba_temp_files a WHERE a.tablespace_name = 'TEMP' GROUP BY a.tablespace_name ) x, (SELECT SUM(b.bytes_used/1024/1024) used_mb, SUM(b.bytes_free/1024/1024) free_mb FROM v$temp_space_header b WHERE b.tablespace_name = 'TEMP' GROUP BY b.tablespace_name );

To find out the percentage utilisation of the temp tablespace, you can use the following query:

Select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

This query calculates the percentage of used blocks in the temp tablespace. The result will give you the percentage of utilisation, allowing you to monitor and manage your temp tablespace usage effectively.

Additionally, you can identify the user and SQL statements responsible for high temporary tablespace usage:

SELECT s.sid || ',' || s.serial# sid_serial, s.username, o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, o.sqladdr address, h.hash_value, h.sql_text FROM v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t WHERE o.session_addr = s.saddr AND o.sqladdr = h.address (+) AND o.tablespace = t.tablespace_name ORDER BY s.sid;

This will help you pinpoint specific users or SQL statements that are consuming a significant amount of temp tablespace.

Frequently asked questions