The Geek Diary

Script/Queries to Monitor Temporary (TEMP) Tablespace Usage in Oracle Database

by

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:

  1. v$sort_segment
  2. 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;

You May Also Like