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.
