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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation