Temporary tablespace

My Oracle 10G temporary tablespace size is increasing abnormally.
it went to 33 GB in just 3 hours.

Why it is getting huge?
LVL 28
imran_fastAsked:
Who is Participating?
 
Stephen LappinSenior TechnologistCommented:
You may be doing large sorts. Have a look at what SQL you are executing.
0
 
adrian_angCommented:
you can use:

SELECT * FROM v$tempseg_usage;

and see what sessions consume most of the temporary tablespace when active.
Then investigate what these sessions are doing.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
select sum(bytes)/1024/1024 size_in_mb from dba_temp_files;
or
select sum(bytes)/1024/1024 size_in_mb  from v$tempfile;

The above should give u the size of your temp tablespace.

You can use the below to see which query is taking the TEMP tablespace..

SELECT *
FROM v$sql
WHERE hash_value IN (
SELECT sql_hash_value  
    FROM v$session s, v$sort_usage u
    WHERE s.saddr = u.session_addr
    AND u.CONTENTS = 'TEMPORARY' ) ;

Thanks

0
 
imran_fastAuthor Commented:
I increased the hash_area_size  and its fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.