Home » RDBMS Server » Server Administration » ORA-1652 Issue in Temp Tablespace (11.2.0.2)
ORA-1652 Issue in Temp Tablespace [message #533703] Thu, 01 December 2011 02:57 Go to next message
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 #533708 is a reply to message #533703] Thu, 01 December 2011 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the temp usage was as follows

Gotten how?

Quote:
Was it becos it was defragmented and was not able to find a contigous segment?

A temporary tablespace is NEVER fragmented.

Regards
Michel
Re: ORA-1652 Issue in Temp Tablespace [message #533720 is a reply to message #533708] Thu, 01 December 2011 03:28 Go to previous messageGo to next message
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 #533723 is a reply to message #533720] Thu, 01 December 2011 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I had a temp usage monitoring script

Are you sure it is correct?

Quote:
So does that mean the whole free space was exhaused bcos there is no such fragmentation logic applicable for TEMP tablespace?

Yes (unless this is not a REAL temporary tablespace).

Quote:
i am not understanding how the inserts statemetns using so much of temp segments?

Post execution plan.
Once again are you sure the reporting script is correct?

Regards
Michel
Re: ORA-1652 Issue in Temp Tablespace [message #533729 is a reply to message #533723] Thu, 01 December 2011 03:49 Go to previous messageGo to next message
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

Re: ORA-1652 Issue in Temp Tablespace [message #533879 is a reply to message #533729] Thu, 01 December 2011 09:37 Go to previous messageGo to next message
emergingdba
Messages: 13
Registered: April 2011
Location: bangalore
Junior Member

Is it a possibility that 28GB of space was used up in 2 mins duration because of a huge insert. But then how and why will insert use temp space?
Re: ORA-1652 Issue in Temp Tablespace [message #533895 is a reply to message #533879] Thu, 01 December 2011 10:07 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 01 December 2011 10:32
...Post execution plan.
Once again are you sure the reporting script is correct?...


Regards
Michel

Previous Topic: Privileges required for export
Next Topic: Removing Auditing
Goto Forum:
  


Current Time: Thu Mar 28 21:34:30 CDT 2024