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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Empty Directories in the Flash Recovery Area (FRA) are not deleted
  2. Where to find ASMLib / oracleasm RPMs for CentOS/RHEL, SUSE, OEL
  3. Oracle ASM 12c – New Features with examples
  4. RMAN Pluggable Database Backup and Recovery in a Multitenant Environment
  5. Oracle Automatic Storage Management (ASM) concepts
  6. How to resize an OCFS2 filesystem on Linux
  7. How to move or rename a datafile in the same ASM diskgroup (Using ASM alias)
  8. ORA-1031 When Connecting Remotely AS SYSDBA
  9. Oracle Database : What Is The Search Order For The LDAP.ORA File
  10. How to Move/Restore Oracle Database to New Host and File System using RMAN

You May Also Like

Primary Sidebar

Recent Posts

  • MySQL: how to figure out which session holds which table level or global read locks
  • Recommended Configuration of the MySQL Performance Schema
  • MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running
  • MySQL: How to kill a Long Running Query using max_execution_time
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary