Jump to Navigation

284 - How to fix ORA-01652

You encounter ORA-01652 while rinning a long SQL.

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.

1. Add a new file to the tablespace.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/d01/app/oracle/oradata/prod/temp02.dbf' SIZE 5G;

## If you need an autoextention on that file.

SQL> ALTER DATABASE TEMPFILE '/d01/app/oracle/oradata/prod/temp02.dbf' AUTOEXTEND ON;

It is no autoextend, but you resize the file :

ALTER DATABASE TEMPFILE '/d01/app/oracle/oradata/prod/temp02.dbf' RESIZE 15G;

Your database has a "8K block size", this is a standard in the 64 bit Linux OS.
It means the file max size is 32GB, the table space can not extend itself if it reaches the max zize.
If TEMP tablespace has been already 32GB and can not extended it any more, even though it has the auto extension option.

2. Check the free blocks on that table.

SQL> select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

if the free block reads '0', it signifies that there is no free space.

3. Parallel Query

When you run Oracle parallel query, you keep receiving ORA-01652 probably. If there is enough space, there was a sort in the parallel query which continues to
cause ORA-01652 to be thrown.  the parallel query coordinator has receives the returned results from the parallel processes as a last step of the OPQ sort.
It was a kind of contention on the processes. One solution is that you can decrease the number of parallel queries.

SQL> show parameters parallel_servers_target
parallel_servers_target integer 5

SQL> alter system set PARALLEL_SERVERS_TARGET=3 scope=both;



Main menu 2

Story | by Dr. Radut