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?

    Requires Free Membership to View

 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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: