[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2225
  • Last Modified:

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?
0
imran_fast
Asked:
imran_fast
2 Solutions
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now