Home » RDBMS Server » Server Administration » temp space error ORA-1652 (Oracle 11.2.0.2.0 hp-aux)
temp space error ORA-1652 [message #546094] Mon, 05 March 2012 05:37 Go to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hello Expert,

We have enough space in the temp tablespace.


SQL> /

TABLESPACE_NAME                 EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
------------------------------- ----------- ------------- ------------ ------------ -------------
TEMP                                    128         58363         4574        53789         58363


SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                 7470464      585472     6884992



But still we are getting error

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Also when looking into V$TEMP_SPACE_HEADER we have below finding.


SQL> select TABLESPACE_NAME,sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024) 
from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES_USED/1024/1024) SUM(BYTES_FREE/1024/1024)
------------------------------ ------------------------- -------------------------
TEMP                                               58367                         0


Please suggest, why we have alert.

[Updated on: Mon, 05 March 2012 06:40] by Moderator

Report message to a moderator

Re: temp space error ORA-1652 [message #546098 is a reply to message #546094] Mon, 05 March 2012 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because there was no space available to satisfy the request at this moment.

Regards
Michel
Re: temp space error ORA-1652 [message #546105 is a reply to message #546098] Mon, 05 March 2012 06:23 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks Michel, yes are right to your point.

But please help me in understanding why V$TEMP_SPACE_HEADER shows zero space left whereas v$sort_segment shows there is enough space in the TEMP tablespace at the same moment of time.

Thanks in Advance.
Re: temp space error ORA-1652 [message #546108 is a reply to message #546105] Mon, 05 March 2012 06:35 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Michel,

I am only trying to understand why has free space not updated in v$temp_space_header?
Re: temp space error ORA-1652 [message #546114 is a reply to message #546108] Mon, 05 March 2012 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They do not report the same thing.
Temporary tablespace space usage is always 100% after a short instance time (unless you never use it). Temporary contains sort segments and also other kind of segments (like gtt).

V$TEMP_SPACE_HEADER
This view displays aggregate information per file per LOCALLY MANAGED temporary tablespace regarding how much space is currently being used and how much is free as identified in the space header.


V$SORT_SEGMENT
This view contains information about every sort segment in a given instance.


Regards
Michel
Re: temp space error ORA-1652 [message #546118 is a reply to message #546114] Mon, 05 March 2012 06:58 Go to previous message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks Michel.
Previous Topic: how to release the space?
Next Topic: ora-00600 internal error code
Goto Forum:
  


Current Time: Fri Sep 19 01:36:38 CDT 2014

Total time taken to generate the page: 0.15503 seconds