Q

Oracle temporary tablespace too large

One reader asks how he can adjust a query to prevent his temporary tablespace file from getting too large.

I have Oracle Database 9i installed on Red Hat Enterprise Linux. My problem is that the Temp file in Oradata Folder has gotten too large. I've recently used the below query that probably causes the file to be so big, and I don't know how to solve the problem. How can I use this query without the problem happening? Is there something I should do with my query?

 set feedback off
set lines 200
set trimspool on
set pages 1000
set pagesize 0
spool q.log
select distinct coumn_1||chr(9)||column_2||chr(9)||column_3
from table1,table2 where
table1.KEY_YEAR=table2.KEY_YEAR and
table1.KEY_cuo=table2.KEY_cuo and
table1.KEY_nber=table2.KEY_nber and
table1.KEY_dec=table2.KEY_dec and
table1.column_3 between '13890101' and '13900519'and table1.column_2 between 7500001 and 8699999 and
table2.column_1 LIKE '%17237%';
spool off

The Temporary tablespace is used for sort operations that are too large to be completed in memory. Oracle breaks the sort operation into smaller pieces and uses the temp tablespace to hold intermediary results. If this query is consuming large amounts of space in the temp tablespace, then you have two options. Code the query differently so as to reduce the sorts or increase your sorting memory. The DISTINCT clause in your query will cause a sort. If you do not need the DISTINCT values, then remove this clause. Your problem description does not indicate how much data is being returned, so it is hard to tell what kind of impact should be expected.

If you want temporarily increase your session's memory for sorting, issue the following before issuing the query:

ALTER SESSION SET sort_area_size=500M;

You can play around with different values to see which works best for you. Note that you will be acquiring memory from the database server so do not ask for too much otherwise you may impact other server operations.

This was first published in August 2011

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

2 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close