ORA-01652: unable to extend temp segment

Some time ago, I remember being asked to investigate regular ORA-01652 error messages. I looked on Metalink and found that a number of people had the same problem. Some of them believed that Oracle was not very good at deciding when it was OK to reuse temporary space. I decided to do a test myself to see whether or not I agreed. The worked example below was run on Oracle 9.2.0.5.0 with a block size of 8192. The database only had 1 tempfile of 20 megabytes:

SQL> l

  1  select file_id, tablespace_name, bytes

  2* from dba_temp_files

SQL> /

 

   FILE_ID TABLESPACE_NAME           BYTES

---------- -------------------- ----------

         1 TEMP                   20971520

 

SQL>

 

Nobody was doing any sorting, which was not surprising as I was the only person using the database:

SQL> l

  1  select username, tablespace, blocks

  2* from v$sort_usage

SQL> /

 

no rows selected

 

SQL>

 

I logged in as USER1, did a sort and waited for the output to appear:

SQL> conn user1/user1

Connected.

SQL> set pause on

SQL> select a.table_name

  2  from dba_tables a, dba_tables b

  3  order by 1

  4  /

TABLE_NAME

------------------------------

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

 

Then, in another session, I looked to see how much sort space was being used:

 

SQL> l

  1  select username, tablespace, blocks

  2* from v$sort_usage

SQL> /

 

USERNAME   TABLESPACE          BLOCKS

---------- --------------- ----------

USER1      TEMP                  1536

 

SQL>

 

USER1 was using 1536 x 8192 = 12582912 bytes of sort space. I returned to USER1's session, interrupted the sort output and ran a new query to check the time for later comparison:

 

SQL> conn user1/user1

Connected.

SQL> set pause on

SQL> select a.table_name

  2  from dba_tables a, dba_tables b

  3  order by 1

  4  /

TABLE_NAME

------------------------------

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

ACCESS$

 

13 rows selected.

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

TIME_NOW

--------

14:36:15

 

SQL>

At this point, USER1 had no way of restarting the output from the sort so the sort space should no longer be required. I then ran the same query as USER2. If it worked for USER1, it should work for USER2 as the space originally used by USER1 should be reusable:

 

SQL> conn user2/user2

Connected.

SQL> set pause on

SQL> l

  1  select a.table_name

  2  from dba_tables a, dba_tables b

  3* order by 1

SQL> /

 

While this was going on, I watched the sort usage grow and noticed that USER1's space was still allocated:

 

  1  select username, tablespace, blocks

  2* from v$sort_usage

SQL> /

 

USERNAME   TABLESPACE          BLOCKS

---------- --------------- ----------

USER2      TEMP                   384

USER1      TEMP                  1536

 

SQL> /

 

USERNAME   TABLESPACE          BLOCKS

---------- --------------- ----------

USER2      TEMP                   512

USER1      TEMP                  1536

 

SQL> /

 

USERNAME   TABLESPACE          BLOCKS

---------- --------------- ----------

USER2      TEMP                   896

USER1      TEMP                  1536

 

SQL>

 

Eventually, USER2's query failed:

 

SQL> conn user2/user2

Connected.

SQL> set pause on

SQL> l

  1  select a.table_name

  2  from dba_tables a, dba_tables b

  3* order by 1

SQL> /

 

select a.table_name

*

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in

tablespace TEMP

SQL>

 

At which point, USER2’s space became available immediately but USER1’s space was still allocated:

 

SQL> l

  1  select username, tablespace, blocks

  2* from v$sort_usage

SQL> /

 

USERNAME   TABLESPACE          BLOCKS

---------- --------------- ----------

USER1      TEMP                  1536

 

SQL>

 

What can you do about this? One site suggests coalescing the tablespace but you cannot do this with a temporary tablespace:

 

SQL> l

  1* alter tablespace temp coalesce

SQL> /

alter tablespace temp coalesce

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY

TABLESPACE

SQL>

 

An hour later, the situation was still the same. V$SORT_SEGMENT showed a similar picture:

 

SQL> l

  1  select tablespace_name, total_blocks,

  2  used_blocks, free_blocks

  3* from v$sort_segment

SQL> /

 

TABLESPACE_NAME  TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

---------------- ------------ ----------- -----------

TEMP                     2432        1536         896

 

SQL>

 

I guess the 896 free blocks were created by USER2’s failed query. As soon as USER1 logged out:

 

SQL> show user

USER is "USER1"

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

TIME_NOW

--------

15:33:08

 

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 – Production

 

The free space returned immediately:

 

SQL> l

  1  select tablespace_name, total_blocks,

  2  used_blocks, free_blocks

 3* from v$sort_segment

SQL> /

 

TABLESPACE_NAME  TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

---------------- ------------ ----------- -----------

TEMP                     2432           0        2432

 

SQL>

So there you have it. If a user interrupts his own sort, Oracle does not realise that his sort space can be reused. However, if a user's sort fails, his sort space is made available again straight away.