The temporary (sort) segment of a given temporary tablespace is created at the time of the first sort operation, which has to write to disk to free up sort space in memory. The first disk sort (after instance startup) creates a sort segment in the temporary tablespace.
Multiple transactions which need a sort on disk can share the same sort segment, however, they cannot share the same extent. The sort segment expands by allocating new extents. The sort extents are not de-allocated while the instance is running, but are marked as free and can be re-used as required. Therefore, the sort segment grows to a certain steady state.
Because the extents do not have to be allocated and de-allocated after each operation, this improves the overall database performance.
There are two views in database that can helps keep track of the free space in the temporary tablespace:
- v$sort_usage or v$tempseg_usage (9i onwards)
The scripts to Monitor Temporary Tablespace Usage
The queries/scripts shown below can be used to show the percentage used in dictionary managed and locally managed temporary tablespace .
For dictionary managed temporary tablespace
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='tbsp_name') s, (select sum(blocks) total_blocks from dba_data_files where tablespace_name='tbsp_name') f;
For locally managed temporary tablespace
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='tbsp_name') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='tbsp_name') f;
How to configure temporary tablesapce
Let’s also see how to configure temporary tablespaces to speed up on-disk sort operations and some diagnostic queries to be used when we want to retrieve information about them. The following steps will demonstrate how to configure temporary tablespaces:
1. Connect as SYSDBA to the database:
CONNECT / AS SYSDBA
2. Examine the number of sorts in the system (from instance startup):
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%sorts%';
3. Examine statistics about temporary tablespace blocks:
COL TABLESPACE_NAME FOR A16 SELECT TABLESPACE_NAME, CURRENT_USERS, TOTAL_BLOCKS, USED_BLOCKS, FREE_BLOCKS, MAX_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS FROM V$SORT_SEGMENT ORDER BY TABLESPACE_NAME;
4. Examine statistics about temporary tablespace extents:
SELECT TABLESPACE_NAME, CURRENT_USERS, EXTENT_SIZE, TOTAL_EXTENTS, USED_EXTENTS, FREE_EXTENTS, EXTENT_HITS FROM V$SORT_SEGMENT ORDER BY TABLESPACE_NAME;
5. Execute a query to retrieve the temporary files:
COL NAME FOR A32 SELECT NAME, STATUS, ENABLED, BYTES, BLOCKS, BLOCK_SIZE FROM V$TEMPFILE;
6. Create a temporary tablespace TEMP_TEST:
CREATE TEMPORARY TABLESPACE TEMP_TEST TEMPFILE '/u01/oradata/TESTDB/temp_test.dbf' SIZE 160M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
7. Assign a temporary tablespace TEMP_TEST to a user:
ALTER USER sh TEMPORARY TABLESPACE TEMP_TEST;
8. Drop temporary tablespace TEMP_TEST and clean up the database:
ALTER USER sh TEMPORARY TABLESPACE TEMP; DROP TABLESPACE TEMP_TEST INCLUDING CONTENTS AND DATAFILES;