Link to home
Start Free TrialLog in
Avatar of imran_fast
imran_fast

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Stephen Lappin
Stephen Lappin
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Naveen Kumar
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

Avatar of imran_fast
imran_fast

ASKER

I increased the hash_area_size  and its fine.