Friday, November 11, 2011

V$SORT_SEGMENT

When a temporary tablespace is first created, it is empty:
 
  1  select current_users, tablespace_name,
  2  used_extents, total_extents
  3* from v$sort_segment
SQL> /
 
CURRENT_USERS TABLESPACE_NAME USED_EXTENTS TOTAL_EXTENTS
------------- --------------- ------------ -------------
            0 TEMPORARY_DATA             0             0
 
SQL>
 
If you kick off a sort:
 
SQL> select a.table_name, b.table_name
  2  from dba_tables a, dba_tables b
  3  order by 1
  4  /
 
Then check the sort segment from a separate session, you can see it growing:
 
SQL> l
  1  select current_users, tablespace_name,
  2  used_extents, total_extents
  3* from v$sort_segment
SQL> /
 
CURRENT_USERS TABLESPACE_NAME USED_EXTENTS TOTAL_EXTENTS
------------- --------------- ------------ -------------
            3 TEMPORARY_DATA          1687          1687
 
SQL>
 
(The CURRENT_USERS column does not seem to show consistent results.)
 
If the sort finishes (or you terminate it as in the example below):
 
SQL> l
  1  select a.table_name, b.table_name
  2  from dba_tables a, dba_tables b
  3* order by 1
SQL> /
select a.table_name, b.table_name
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
SQL>
 
TOTAL_EXTENTS retains its maximum value but USED_EXTENTS goes back to zero:
 
SQL> l
  1  select current_users, tablespace_name,
  2  used_extents, total_extents
  3* from v$sort_segment
SQL> /
 
CURRENT_USERS TABLESPACE_NAME USED_EXTENTS TOTAL_EXTENTS
------------- --------------- ------------ -------------
            0 TEMPORARY_DATA             0          1755
 
SQL>

No comments: