Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Oracle database performance problems and tuning

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

How to fix this ERROR: ORACLE execute error: ORA-01652: unable to extend temp segment by 128 in tablespace PREP_TNZTEMP_1.after increased the space again facing same issue