Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to monitor temporary tablespace usage in Oracle?
Problem:
You want to monitor the usage of the temporary tablespace in Oracle.
Solution:
We can use the following query to find out the used and free space in a temporary tablespace.
We will begin with identifying temporary table space names.
Example
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';
Output
TEMP
Next up we will use below SQL to identify the used and free space in temporary tablespace.
Example
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 );
Output
TEMP 4600 4568 32
We will identify the user and the SQL statements responsible for the high temporary tablespace usage.
Example
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;
We can use the following query to find out which sessions are using space in the temporary tablespace.
Example
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;
7K+ Views
