• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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;

Filed Under: oracle

Some more articles you might also be interested in …

  1. New Oracle Net features in version 12c
  2. How to find daily and hourly archive log generation in Oracle Database
  3. Oracle Data Guard Security Enhancement – SYSDG Administration Privilege
  4. Oracle Data Guard 12c – Creating a Physical Standby from Primary
  5. How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command
  6. How to move a Datafile to a different Location on a Physical Standby Database
  7. How to Change the Default Home Page of Oracle HTTP Server
  8. What are Oracle Database Valut Schemas
  9. How to relocate the redo log files to a different location on disk
  10. New Connections to the Database lead to ORA-12518 or TNS-12518

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright