| Ran out of temp tablespace [message #141897] |
Wed, 12 October 2005 13:56  |
prekida
Messages: 5
Registered: March 2005
|
Junior Member |
|
|
Hi,
I have approximately 1G temp tablespace. when trying to create
index of size 647M - it complained that it ran out of temp tablespace. No other users were there other than me.
I happened to query v$sort_segment when the index was getting created. It started gradually reducing free extents to 0
Question is why a 1G temp tablespace could not accomadate
creation of 647M index
Please advise
Your help is greatly appreciated
|
|
|
|
| Re: Ran out of temp tablespace [message #141911 is a reply to message #141897] |
Wed, 12 October 2005 15:23  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
How do you know that the index was 647 MB if it was not able to be created so that you could measure its size?
Also, many operations require essentially two copies of the index. Or 3. An old copy, the new copy, then merging of old with new to form the final, real copy. You can read more about that in looking into direct path loading, such as with sqlldr.
|
|
|
|
| Re: Ran out of temp tablespace [message #142085 is a reply to message #141897] |
Thu, 13 October 2005 07:26 |
prekida
Messages: 5
Registered: March 2005
|
Junior Member |
|
|
Thank you for your response.
The following was used to measure on the size of the index:
select substr(segment_name,1,30) INDEX_NAME,
owner OWNER, bytes/1024/1024 "Size in MB"
from dba_segments d
where d.segment_name) like '<involved_segment_name>'
and lower(d.OWNER) in ( '<involved_owner>')
and d.SEGMENT_TYPE in ('INDEX');
The following was used to measure the growth of the temp tablespace
select * from v$sort_segment
When you say
>> many operations require essentially two copies of the index. Or 3.
>>
- are these copies stored in the SGA or PGA ? - just wanted to make sure if there is any way i could account for the bytes used
in the memory or in the disk
Thank you for your help and advise
|
|
|
|
| Re: Ran out of temp tablespace [message #142458 is a reply to message #141897] |
Fri, 14 October 2005 11:39 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
They are stored temporarily until the index rebuild or creation process is complete, at which point they are deleted. They would be stored like everything else, which is to say it would be in RAM (SGA or PGA, sort being PGA) as much as could fit with the overflow going into the temp tablespace.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
