How to monitor temporary tablespace usage in Oracle?

OracleSoftware & CodingProgramming

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;

raja
Published on 05-Dec-2020 10:28:55
Advertisements