Temporary tablespace management is a critical aspect of Oracle Database administration. When TEMP tablespace fills up, it can cause queries to fail, sessions to hang, and overall database performance to degrade. In this guide, we’ll explore various SQL queries that help you monitor and troubleshoot TEMP tablespace usage effectively, with proper output formatting for better readability.<\/p>\n\n\n\n
The TEMP tablespace in Oracle is used for temporary operations such as sorting, hash joins, index creation, and other operations that require temporary storage. Unlike permanent tablespaces, data in TEMP is transient and doesn’t need to be backed up. However, monitoring its usage is essential to prevent performance bottlenecks.<\/p>\n\n\n\n
The most straightforward way to check your TEMP tablespace usage is with this comprehensive query:<\/p>\n\n\n\n
SET LINESIZE 200\nSET PAGESIZE 100\nCOLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'\nCOLUMN \"Total Size [GB]\" FORMAT 999,990.99\nCOLUMN \"Used_size[GB]\" FORMAT 999,990.99\nCOLUMN \"Free_size[GB]\" FORMAT 999,990.99\nCOLUMN \"Used Percentage\" FORMAT 990.99\n\nSELECT a.tablespace_name,\n ROUND((c.total_blocks*b.block_size)\/1024\/1024\/1024,2) \"Total Size [GB]\",\n ROUND((a.used_blocks*b.block_size)\/1024\/1024\/1024,2) \"Used_size[GB]\",\n ROUND(((c.total_blocks-a.used_blocks)*b.block_size)\/1024\/1024\/1024,2) \"Free_size[GB]\",\n ROUND((a.used_blocks\/c.total_blocks)*100,2) \"Used Percentage\"\nFROM V$sort_segment a,\n dba_tablespaces b,\n (SELECT tablespace_name, SUM(blocks) total_blocks \n FROM dba_temp_files \n GROUP BY tablespace_name) c\nWHERE a.tablespace_name=b.tablespace_name \n AND a.tablespace_name=c.tablespace_name;<\/code><\/pre>\n\n\n\nThis query provides a complete overview showing total size, used space, free space, and usage percentage in gigabytes with properly aligned columns.<\/p>\n\n\n\n
Calculating TEMP Usage Percentage<\/h2>\n\n\n\n
For a quick percentage check of your TEMP tablespace, use this simple query:<\/p>\n\n\n\n
SET LINESIZE 100\nSET PAGESIZE 50\nCOLUMN \"percent used\" FORMAT 990.99 HEADING 'TEMP Usage %'\n\nSELECT (s.tot_used_blocks\/f.total_blocks)*100 AS \"percent used\"\nFROM (SELECT SUM(used_blocks) tot_used_blocks\n FROM v$sort_segment \n WHERE tablespace_name='TEMP') s,\n (SELECT SUM(blocks) total_blocks\n FROM dba_temp_files \n WHERE tablespace_name='TEMP') f;<\/code><\/pre>\n\n\n\nThis gives you an immediate view of how much of your TEMP tablespace is currently allocated.<\/p>\n\n\n\n
Identifying Top TEMP Consumers<\/h2>\n\n\n\n
When TEMP usage spikes, you need to quickly identify which sessions are consuming the most space. This query shows the top 10 sessions by TEMP usage:<\/p>\n\n\n\n
SET LINESIZE 250\nSET PAGESIZE 100\nCOLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'\nCOLUMN sid FORMAT 9999 HEADING 'SID'\nCOLUMN serial# FORMAT 999999 HEADING 'Serial#'\nCOLUMN program FORMAT A25 HEADING 'Program'\nCOLUMN module FORMAT A20 HEADING 'Module'\nCOLUMN action FORMAT A15 HEADING 'Action'\nCOLUMN \"DB Username\" FORMAT A15 HEADING 'DB User'\nCOLUMN osuser FORMAT A15 HEADING 'OS User'\nCOLUMN \"Used MB\" FORMAT 999,999.99 HEADING 'TEMP Used (MB)'\nCOLUMN sql_text FORMAT A50 HEADING 'SQL Text' WORD_WRAPPED\n\nSELECT * FROM\n(SELECT d.tablespace_name,\n a.sid,\n a.serial#,\n a.program,\n a.module,\n a.action,\n a.username \"DB Username\",\n a.osuser,\n ROUND((b.blocks*d.block_size)\/1024\/1024,2) \"Used MB\",\n c.sql_text\nFROM v$session a, \n v$tempseg_usage b, \n v$sqlarea c,\n dba_tablespaces d\nWHERE a.saddr = b.session_addr \n AND c.address= a.sql_address \n AND c.hash_value = a.sql_hash_value \n AND d.tablespace_name=b.tablespace \nORDER BY b.tablespace, b.blocks DESC)\nWHERE rownum <=10;<\/code><\/pre>\n\n\n\nThis query is invaluable during troubleshooting, as it shows you not only who is using TEMP space but also what SQL they’re running.<\/p>\n\n\n\n
Monitoring Active Sessions Using TEMP<\/h2>\n\n\n\n
To see which sessions are currently consuming significant TEMP space (over 1GB in this example), use:<\/p>\n\n\n\n
SET LINESIZE 250\nSET PAGESIZE 100\nCOLUMN sysdate FORMAT A20 HEADING 'Current Time'\nCOLUMN username FORMAT A15 HEADING 'Database User'\nCOLUMN sid FORMAT 9999 HEADING 'SID'\nCOLUMN serial# FORMAT 999999 HEADING 'Serial#'\nCOLUMN osuser FORMAT A15 HEADING 'OS User'\nCOLUMN mb_used FORMAT 999,999.99 HEADING 'TEMP Used (MB)'\nCOLUMN sql_text FORMAT A60 HEADING 'SQL Statement' WORD_WRAPPED\n\nSELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') sysdate,\n a.username, \n a.sid, \n a.serial#, \n a.osuser, \n (b.blocks*d.block_size)\/1048576 mb_used, \n c.sql_text\nFROM v$session a, \n v$tempseg_usage b, \n v$sqlarea c,\n (SELECT block_size FROM dba_tablespaces WHERE tablespace_name='TEMP') d\nWHERE b.tablespace = 'TEMP'\n AND a.saddr = b.session_addr\n AND c.address= a.sql_address\n AND c.hash_value = a.sql_hash_value\n AND (b.blocks*d.block_size)\/1048576 > 1024\nORDER BY mb_used DESC;<\/code><\/pre>\n\n\n\nThis helps you focus on sessions that are heavy TEMP users and may need investigation or termination.<\/p>\n\n\n\n
Checking Sort Segment Usage by User<\/h2>\n\n\n\n
To see which users are performing operations that require TEMP space:<\/p>\n\n\n\n
SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN username FORMAT A15 HEADING 'Username'\nCOLUMN sid FORMAT 9999 HEADING 'SID'\nCOLUMN serial# FORMAT 999999 HEADING 'Serial#'\nCOLUMN tablespace FORMAT A15 HEADING 'Tablespace'\nCOLUMN contents FORMAT A10 HEADING 'Contents'\nCOLUMN extents FORMAT 999,999 HEADING 'Extents'\nCOLUMN blocks FORMAT 999,999,999 HEADING 'Blocks Used'\n\nSELECT s.username,\n s.sid,\n s.serial#,\n u.tablespace, \n u.contents, \n u.extents, \n u.blocks\nFROM v$session s, v$sort_usage u\nWHERE s.saddr=u.session_addr\nORDER BY u.blocks DESC;<\/code><\/pre>\n\n\n\nViewing Free Space in TEMP Tablespace<\/h2>\n\n\n\n
To understand how much free space remains in your TEMP tablespace:<\/p>\n\n\n\n
SET LINESIZE 150\nSET PAGESIZE 50\nCOLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'\nCOLUMN FreeSpaceInGB FORMAT 999,990.99 HEADING 'Free Space (GB)'\nCOLUMN UsedSpaceInGB FORMAT 999,990.99 HEADING 'Used Space (GB)'\nCOLUMN TotalSpaceInGB FORMAT 999,990.99 HEADING 'Total Space (GB)'\n\nSELECT tablespace_name,\n (free_blocks*8)\/1024\/1024 FreeSpaceInGB,\n (used_blocks*8)\/1024\/1024 UsedSpaceInGB,\n (total_blocks*8)\/1024\/1024 TotalSpaceInGB\nFROM v$sort_segment \nWHERE tablespace_name LIKE '%TEMP%';<\/code><\/pre>\n\n\n\nComprehensive TEMP Status Query<\/h2>\n\n\n\n
For a detailed view including extent management information:<\/p>\n\n\n\n
SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN \"Status\" FORMAT A10 HEADING 'Status'\nCOLUMN \"Name\" FORMAT A20 HEADING 'Tablespace Name'\nCOLUMN \"Type\" FORMAT A12 HEADING 'Type'\nCOLUMN \"ExtManag\" FORMAT A10 HEADING 'Extent Mgmt'\nCOLUMN \"Size (M)\" FORMAT A20 HEADING 'Size (MB)'\nCOLUMN \"Used (M)\" FORMAT A40 HEADING 'Used\/Total (MB)'\nCOLUMN \"Used %\" FORMAT A10 HEADING 'Used %'\n\nSELECT d.status \"Status\", \n d.tablespace_name \"Name\", \n d.contents \"Type\", \n d.extent_management \"ExtManag\",\n TO_CHAR(NVL(a.bytes \/ 1024 \/ 1024, 0),'99,999,990.900') \"Size (M)\", \n TO_CHAR(NVL(t.bytes, 0)\/1024\/1024,'99999,999.999') ||'\/'||\n TO_CHAR(NVL(a.bytes\/1024\/1024, 0),'99999,999.999') \"Used (M)\",\n TO_CHAR(NVL(t.bytes \/ a.bytes * 100, 0), '990.00') \"Used %\"\nFROM sys.dba_tablespaces d, \n (SELECT tablespace_name, SUM(bytes) bytes \n FROM dba_temp_files \n GROUP BY tablespace_name) a,\n (SELECT tablespace_name, SUM(bytes_cached) bytes \n FROM v$temp_extent_pool \n GROUP BY tablespace_name) t\nWHERE d.tablespace_name = a.tablespace_name(+) \n AND d.tablespace_name = t.tablespace_name(+)\n AND d.extent_management LIKE 'LOCAL' \n AND d.contents LIKE 'TEMPORARY';<\/code><\/pre>\n\n\n\nAdvanced: Top 10 Sessions with Largest TEMP Usage<\/h2>\n\n\n\n
For detailed session-level analysis with timing information:<\/p>\n\n\n\n
SET LINESIZE 250\nSET PAGESIZE 100\nCOLUMN sid FORMAT 9999 HEADING 'SID'\nCOLUMN status FORMAT A10 HEADING 'Status'\nCOLUMN sesshash FORMAT 9999999999 HEADING 'Session Hash'\nCOLUMN sorthash FORMAT 9999999999 HEADING 'Sort Hash'\nCOLUMN username FORMAT A15 HEADING 'Username'\nCOLUMN tablespace FORMAT A15 HEADING 'Tablespace'\nCOLUMN mbused FORMAT 999,999.99 HEADING 'MB Used'\nCOLUMN noexts FORMAT 999,999 HEADING 'Extents'\nCOLUMN proginfo FORMAT A30 HEADING 'Program\/Module' TRUNCATE\nCOLUMN lastcallet FORMAT A15 HEADING 'Last Call ET'\n\nSELECT * FROM (\n SELECT s.sid,\n s.status,\n s.sql_hash_value sesshash,\n u.SQLHASH sorthash,\n s.username,\n u.tablespace,\n SUM(u.blocks*p.value\/1024\/1024) mbused,\n SUM(u.extents) noexts,\n NVL(s.module,s.program) proginfo,\n FLOOR(last_call_et\/3600)||':'||\n FLOOR(MOD(last_call_et,3600)\/60)||':'||\n MOD(MOD(last_call_et,3600),60) lastcallet\n FROM v$sort_usage u,\n v$session s,\n v$parameter p\n WHERE u.session_addr = s.saddr\n AND p.name = 'db_block_size'\n GROUP BY s.sid, s.status, s.sql_hash_value, u.sqlhash, \n s.username, u.tablespace, NVL(s.module,s.program),\n FLOOR(last_call_et\/3600)||':'||\n FLOOR(MOD(last_call_et,3600)\/60)||':'||\n MOD(MOD(last_call_et,3600),60)\n ORDER BY 7 DESC, 3\n)\nWHERE rownum < 11;<\/code><\/pre>\n\n\n\nQuick TEMP Size Query<\/h2>\n\n\n\n
For a simple size check:<\/p>\n\n\n\n
SET LINESIZE 100\nSET PAGESIZE 50\nCOLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'\nCOLUMN mb FORMAT 999,999.99 HEADING 'Size (MB)'\n\nSELECT tablespace_name, \n SUM(bytes)\/1024\/1024 mb\nFROM dba_temp_files\nGROUP BY tablespace_name;<\/code><\/pre>\n\n\n\nBest Practices for TEMP Tablespace Management<\/h2>\n\n\n\n
Regular Monitoring<\/strong>: Schedule these queries to run regularly, especially the percentage usage and top consumers queries. Set up alerts when usage exceeds 80%.<\/p>\n\n\n\nUnderstand Your Workload<\/strong>: Know which operations in your database typically use TEMP space heavily. Batch jobs, reporting queries, and data warehouse operations are common culprits.<\/p>\n\n\n\nSize Appropriately<\/strong>: Ensure your TEMP tablespace is sized adequately for your workload. A general rule of thumb is 10-20% of your database size, but this varies greatly by application.<\/p>\n\n\n\nInvestigate Long-Running Queries<\/strong>: Sessions that hold TEMP space for extended periods may indicate poorly optimized SQL that needs tuning.<\/p>\n\n\n\nConsider Multiple TEMP Tablespaces<\/strong>: For very large databases, consider creating multiple temporary tablespaces to distribute load and reduce contention.<\/p>\n\n\n\nCreating a Monitoring Script<\/h2>\n\n\n\n
You can combine these queries into a single monitoring script for regular execution:<\/p>\n\n\n\n
-- TEMP Tablespace Monitoring Script\n-- Run this script for comprehensive TEMP usage analysis\n\nSET ECHO OFF\nSET FEEDBACK OFF\nSET VERIFY OFF\nSET HEADING ON\nSET TIMING OFF\n\nPROMPT \nPROMPT ========================================\nPROMPT TEMP TABLESPACE USAGE REPORT\nPROMPT ========================================\nPROMPT \nPROMPT Report Generated: \nSELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;\nPROMPT \n\nPROMPT \nPROMPT ========================================\nPROMPT 1. Overall TEMP Usage Summary\nPROMPT ========================================\nPROMPT \n\nSET LINESIZE 150\nSET PAGESIZE 100\nCOLUMN tablespace_name FORMAT A20 HEADING 'Tablespace'\nCOLUMN \"Total Size [GB]\" FORMAT 999,990.99\nCOLUMN \"Used_size[GB]\" FORMAT 999,990.99\nCOLUMN \"Free_size[GB]\" FORMAT 999,990.99\nCOLUMN \"Used Percentage\" FORMAT 990.99\n\nSELECT a.tablespace_name,\n ROUND((c.total_blocks*b.block_size)\/1024\/1024\/1024,2) \"Total Size [GB]\",\n ROUND((a.used_blocks*b.block_size)\/1024\/1024\/1024,2) \"Used_size[GB]\",\n ROUND(((c.total_blocks-a.used_blocks)*b.block_size)\/1024\/1024\/1024,2) \"Free_size[GB]\",\n ROUND((a.used_blocks\/c.total_blocks)*100,2) \"Used Percentage\"\nFROM V$sort_segment a,\n dba_tablespaces b,\n (SELECT tablespace_name, SUM(blocks) total_blocks \n FROM dba_temp_files \n GROUP BY tablespace_name) c\nWHERE a.tablespace_name=b.tablespace_name \n AND a.tablespace_name=c.tablespace_name;\n\nPROMPT \nPROMPT ========================================\nPROMPT 2. Top 10 TEMP Consumers\nPROMPT ========================================\nPROMPT \n\nSET LINESIZE 250\nCOLUMN sid FORMAT 9999 HEADING 'SID'\nCOLUMN serial# FORMAT 999999 HEADING 'Serial#'\nCOLUMN \"DB Username\" FORMAT A15 HEADING 'DB User'\nCOLUMN osuser FORMAT A12 HEADING 'OS User'\nCOLUMN \"Used MB\" FORMAT 999,999.99 HEADING 'TEMP (MB)'\nCOLUMN program FORMAT A20 HEADING 'Program' TRUNCATE\n\nSELECT * FROM\n(SELECT d.tablespace_name,\n a.sid,\n a.serial#,\n a.username \"DB Username\",\n a.osuser,\n a.program,\n ROUND((b.blocks*d.block_size)\/1024\/1024,2) \"Used MB\"\nFROM v$session a, \n v$tempseg_usage b, \n dba_tablespaces d\nWHERE a.saddr = b.session_addr \n AND d.tablespace_name=b.tablespace \nORDER BY b.blocks DESC)\nWHERE rownum <=10;\n\nPROMPT \nPROMPT ========================================\nPROMPT End of Report\nPROMPT ========================================\n\nSET FEEDBACK ON\nSET VERIFY ON<\/code><\/pre>\n\n\n\nConclusion<\/h2>\n\n\n\n
Monitoring TEMP tablespace usage is essential for maintaining a healthy Oracle database. The queries provided in this guide, with proper formatting directives, give you multiple perspectives on TEMP usage from high-level percentage views to detailed session-level analysis. The formatting commands (SET LINESIZE, SET PAGESIZE, COLUMN) ensure the output is readable and professional, making it easier to identify issues quickly.<\/p>\n\n\n\n
By regularly running these queries and understanding the patterns in your database, you can proactively manage TEMP space and prevent performance issues before they impact your users. Remember, when you identify sessions consuming excessive TEMP space, always investigate the SQL being executed first. Often, the solution is query optimization rather than simply adding more TEMP space.<\/p>\n","protected":false},"excerpt":{"rendered":"
Temporary tablespace management is a critical aspect of Oracle Database administration. When TEMP tablespace fills up, it can cause queries to fail, sessions to hang, and overall database performance to degrade. In this guide, we’ll explore various SQL queries that help you monitor and troubleshoot TEMP tablespace usage effectively, with proper output formatting for better […]<\/p>\n","protected":false},"author":1,"featured_media":5426,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5424","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5424","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5424"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5424\/revisions"}],"predecessor-version":[{"id":5425,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5424\/revisions\/5425"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5426"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}