RE: Temporary tablespace usage
Date: Wed, 26 Mar 2014 10:07:11 -0700
Message-ID: <ABBB652791780D439917574092C4BB8601511203D7_at_NPEXCHMB102.tdc.internal>
Hi Peter, yes, the TOTAL is showing you how large the TEMP tablespace is right now. However, it is not necessarily the size since the last database restart, but it is the total size of all files that make up the TEMP tablespace. Based on what I see I would guess you have 1 file in the TEMP space and it is at its max size (32GB for a datafile with 8K block size). If you have autoextend turned on then there was a statement - or statements - that pushed it all the way to 32GB.
I don't know of any way to calculate how much TEMP would be required to complete the failed transaction. You could try and add a datafile of 1GB (if autoextend is on) and see how much it grows to complete the transaction. In my experience you would want to look at the query first and make sure it is performing optimally. I've seen queries that were fine before and then, with bad stats and query plan, they used an excess of TEMP space.
Michael Cunningham
Senior Database Administrator
The Doctors' Company
707.226.0221 - desk
707.337.0184 - cell
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schauss, Peter (ESS)
Sent: Wednesday, March 26, 2014 9:42 AM
To: oracle-l
Subject: Temporary tablespace usage
This is Oracle 11.2.0.3 running on 32 bit Windows. We have had an "ORA-1652 - Unable to extend temp segment ..." and I am trying to get an idea of how much larger the temp tablespace needs to be based on previous use.
When I run this query:
select total_blocks*8192/1000000000 Total,
free_blocks*8192/1000000000 Free,
current_users,
max_sort_size*8192/1000000000 Max
from v$sort_segment
where tablespace_name='TEMP';
I get:
TOTAL FREE CURRENT_USERS MAX 35.06962432 35.060187136 9 0.273932288
Does this mean that the high water mark in TEMP since the last database restart is 35 gb and the maximum use by any session is .27 gb?
Is there anything other than the sort segment which uses space in TEMP?
Thanks,
Peter Schauss
--
http://www.freelists.org/webpage/oracle-l
Confidentiality Notice: This message and any attachments hereto may contain confidential and privileged communications or information and/or attorney client communications or work-product protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s). If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this e-mail or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this e-mail in error, please notify the sender immediately and permanently delete this e-mail.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 26 2014 - 18:07:11 CET