Question: I have been getting the ORA-1652
errors, and I have no more disk to allocate to my TEMP tablespace:
Tue Dec 23 07:38:16 2015
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Tue Dec 23 07:51:11 2015
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
I have waited for SMON to clean out the un-used TEMP segments, and
re-starting the instance did not stop the ORA-1652 errors. It's an
emergency. How do I remove the temp segments?
Answer: Normally, you would just add disk to
TEMP to avoid the ORA-1652 error, but you can also wait for SMON to
clean-up the TEMP segment.
See these important notes for
fixing the ORA-1652 unable to extend error.
You can check for held
TEMP segments with this query:
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
Oracle docs note this about ORA-01652:
-
ORA-01652:
unable to extend temp segment by
string in
tablespace string
-
Cause: Failed to allocate an extent of the required number
of blocks for a temporary segment in the tablespace indicated.
-
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add
one or more files to the tablespace indicated.
MOSC
has a very detailed and informative article concerning ORA-01652 and RAC.
There is some troubleshooting required with ORA-01652 in RAC because there are
two common causes in this area.
First ORA-01652 may occur because there is
simply no space available in the temp tablespace of which is being used.
The second cause of ORA-01652 may have to do with the local temp segment not
being able to extent space even though there is space in other instances.
To trouble shoot for ORA-01652, and find
out which of the above scenarios are causing ORA-01652 use this query offered by
MOSC:
select sum(free_blocks)
from gv$sort_segment
where tablespace_name = '<TEMP TABLESPACE NAME>'
You will know that the first scenario is
causing ORA-01652 to be thrown if the free block reads '0' because it signifies
that there is no free space.
If there is a good amount of space, you know
that there is another cause for ORA-01652, and it is probably the second
scenario. It is important to note that in a non-RAC environment, local
instances are not able to extend the temp segments, so in the RAC environment,
ORA-01652 has to be handled differently. If you are experiencing ORA-01652
in a non-RA environment, be aware that every SQL making use of the tablespace
can fail.
In RAC, more sort segment space can be used
from other instances, which can help resolve ORA-01652 more easily. Try
using the query below:
select inst_id, tablespace_name,
total_blocks, used_blocks, free_blocks
from gv$sort_segment;
Basically, you can then find out how much temp
segment space can be used for each instance by viewing the total_blocks,
and the used_blocks can reveal the space which has been used so far, and
the free_blocks gives the amount of space allocated to this
particular instance. This being, to resolve ORA-01652, you can
check out that used_blocks = total_blocks and
free_blocks = 0 will probably show for the instance, and ORA-01652
will be shown multiple times within the alert log.
This basically means that free space from other instances is being requested,
and typically signifies that there is instance contention. Instance
contention within the temporary space can make the instance take more time to
process.
In sever cases, a slowdown may occur, in which
you might want try one of the following work-arounds:
- Increase size of the temp tablespace
- Increase sort_area_size and/or
pga_aggregate_target
However, remember to not use the RAC feature of
DEFAULT temp space.
If ORA-01652 is causing the slowdown, SMON will
probably not be able to process the sort segment requests, you should try to
diagnose the contention:
- Output from the following query
periodically during the problem:
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;
- Global hanganalyze and systemstate
dumps
Here is another example of ORA-01652 from an
article
regarding ORA-12801.
Question:
I understand ORA-01652
is usually caused by running out of space
When I run Oracle parallel query, I keep receiving ORA-01652, why?
Answer:
In this case, there was
a sort in the parallel query which continues to cause ORA-01652 to be thrown.
Remember, the parallel query coordinator has receives the returned results from
the parallel processes as a last step of the OPQ sort. This being, you
should be able to resolve ORA-01652 by increasing TEMP, and perhaps also the
sort_area_size. For advice on this, refer to the statement below:
If this job is running batch, you
can do this with an alter session command, as this this case, to one gig:
alter session set sort_area_size = 1,048,576,000