| ORA-1652 Issue in Temp Tablespace [message #533703] |
Thu, 01 December 2011 02:57  |
deepa_balu
Messages: 74
Registered: March 2005
|
Member |
|
|
I am having a very big Temp Tablespace with 4 tempfiles each of 32GB.
Usually there is nightly run involving very big tables.
When i got the error
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
, the temp usage was as follows
Tablespace name Total MB UsedMB FreeMB
TEMP 128818 100735 28083
Only 100GB was used and there was about 28GB free space in Temp.
But still i was getting the error.
Was it becos it was defragmented and was not able to find a contigous segment? Is it applicable for temp tablespace also..
|
|
|
|
|
|
| Re: ORA-1652 Issue in Temp Tablespace [message #533720 is a reply to message #533708] |
Thu, 01 December 2011 03:28  |
deepa_balu
Messages: 74
Registered: March 2005
|
Member |
|
|
I had a temp usage monitoring script and that script showed the above usage 2 mins before the Error occurred.
So does that mean the whole free space was exhaused bcos there is no such fragmentation logic applicable for TEMP tablespace?
Also my monitoring script was capturing the sql statments that was using the temp tablespace and i saw it was reporting insert statements.
INSERT /*+APPEND NOLOGGING PARALLEL(tablea ,6)*/ into tablea select *
from tableb where starttime between ? and ?
i am not understanding how the inserts statemetns using so much of temp segments?
[Updated on: Thu, 01 December 2011 03:30] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: ORA-1652 Issue in Temp Tablespace [message #533729 is a reply to message #533723] |
Thu, 01 December 2011 03:49 |
deepa_balu
Messages: 74
Registered: March 2005
|
Member |
|
|
yes i am using the following query
SELECT a.tablespace_name TABLESPACE, d.mb_total,
SUM(a.used_blocks *d.block_size) / 1024 / 1024 mb_used,
d.mb_total -SUM(a.used_blocks *d.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment a,
(SELECT b.name, c.block_size, SUM(c.bytes) / 1024 / 1024 mb_total
FROM v$tablespace b, v$tempfile c
WHERE b.ts# = c.ts#
GROUP BY b.name, c.block_size)
d
WHERE a.tablespace_name = d.name
GROUP BY a.tablespace_name, d.mb_total
[Updated on: Thu, 01 December 2011 04:01] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
