Oracle Error:
ORA-01652:
unable to extend temp segment by string in tablespace string
Eg: ORA-1652:
unable to extend temp segment by 128 in tablespace TEMP
Cause:
In RAC
environment, 2 reasons could cause this error.
Case-1: No space available in the temp table space of
which is being used.
Case-2: The
second cause may have to do with the local temp segment not being able to
extent space even though there is space in other instances.
Action Plan:
In general, Use
ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the
tablespace indicated. 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.
CASE 1:
Find the free
space in temp tablespace:
SQL> SELECT
tablespace_name, file_name, bytes/1024/1024 size_in_mb from dba_temp_files;
SQL> select
sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP
TABLESPACE NAME';
If the free
block reads results '0', it signifies that there is no free space.
Work-around:
Add a new temp
file to tablespace or resize the existing temp file.
To add a new
file,
SQL> ALTER
TABLESPACE ADD DATAFILE|TEMPFILE 'full path and
file name' SIZE integer;
To resize the
existing temp file,
SQL> ALTER
DATABASE DATAFILE|TEMPFILE ‘/u03/oradata/ temp02. dbf’ RESIZE 200M;
CASE 2:
In RAC, more
sort segment space can be used from other instances, which can help resolve
ORA-01652 more easily.
SQL> select
inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from
gv$sort_segment;
Where total_blocks
& used_blocks ---> total temp segment space used &
free_blocks
---> amount of space allocated to this particular instance.
If used_blocks =
total_blocks and free_blocks = 0, it results in ORA-01652 error.
If ORA-01652 is
causing the slowdown, SMON will probably not be able to process the sort
segment requests. Try one of the following work-around:
1) Increase size
of the temp tablespace
SQL> ALTER
DATABASE DATAFILE|TEMPFILE ‘/u03/oradata/ userdata02. dbf’ RESIZE 200M;
2) Increase
sort_area_size and/or pga_aggregate_target
SQL> alter
system set sort_area_size = 32765 scope=both;
SQL> alter
session set sort_area_size = 32765 (to modify in the current session itself)
SORT_AREA_SIZE
specifies (in bytes) the maximum amount of memory Oracle will use for a sort.
After the sort is complete, but before the rows are returned, Oracle releases
all of the memory allocated for the sort, except the amount specified by the
SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases
the remainder of the memory.
SQL> select
inst_id, file_id, extents_cached, extents_used from gv$temp_extent_pool order
by 1,2;
In RAC, extents
are allocated from all temporary files uniformly. There are also changes to
file header block during this operation. This is one of the reason, to create
many temporary files in RAC. If you have 24 nodes in your RAC cluster, yes,
that would imply that you would have to create 24 temp files to the TEMP table
space.
In Server Manager, click on Features and then Click on Add Features.
Select Telnet Client from the available list of features, then click Next.
Installation in Progress...
Installation is Completed .
Now run the telnet command and as you expect, it is recognized by Windows Server 2008 and you can start using it to connect to your telnet servers.
