 |
|
ORA-01652
tips
Oracle Error Tips by Burleson Consulting
|
Question: What is the cause of the ORA-01652 error?
Answer: The Oracle oerr utility note this about
the ORA-01652 error:
-
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.
First, see these important notes for
fixing the ORA-1652 unable to extend error.
If you have purchased the packs to use the AWR, see
dba_hist_undostat tips , which can
help unveil how Oracle attempted to used all available undo before aborting
with the ORA-01652 error.
This will add space to the TEMP tablespace:
alter tablespace
temp
add tempfile
'e:\oracle\app\oradata\zoom\temp02.dbf'
size 1g
autoextend on;
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
see code
depot for full scripts
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
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:
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
|
