How to find the SQL that using temporary tablespace in Oracle

16 Oct

Do the following if you want to find out who and what is using temporary tablespace in the databas

1: Check the space usage

SELECT b.Total_MB,
b.Total_MB – ROUND (a.used_blocks * 8 / 1024) Current_Free_MB,
ROUND (used_blocks * 8 / 1024) Current_Used_MB,
ROUND (max_used_blocks * 8 / 1024) Max_used_MB
FROM v$sort_segment a,
(SELECT ROUND (SUM (bytes) / 1024 / 1024) Total_MB FROM dba_temp_files)
b;

2: Which session is using temporary tablespace

COL hash_value FOR a40
COL tablespace FOR a10
COL username FOR a15
SET LINESIZE 132 PAGESIZE 1000

SELECT s.SID,
s.username,
u.TABLESPACE,
s.sql_hash_value || ‘/’ || u.sqlhash hash_value,
u.segtype,
u.CONTENTS,
u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr = u.session_addr
ORDER BY u.blocks;

2: Check sql, sorts, rows_processed (switch out XX with s.sid and USERXX with s.username from previous step)

SELECT *
FROM v$sql
WHERE hash_value IN (SELECT hash_value
FROM v$open_cursor
WHERE sid = XX)
AND sorts > 0
AND PARSING_SCHEMA_NAME = ‘USERXX’
ORDER BY rows_processed / executions;

3: In column sql_fulltext you can see which select is run and using temporary tablespace

 

Another example below:

http://alexzeng.wordpress.com/2012/06/13/how-to-find-the-sql-that-using-lots-of-temp-tablespace-in-oracle/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: