RE: Temporary tablespace usage
- From: "Cunningham, Mike" <mcunningham@xxxxxxxxxxxxxx>
- To: "peter.schauss@xxxxxxx" <peter.schauss@xxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Mar 2014 10:07:11 -0700
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
