Sunday, June 10, 2012

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.

No comments: