HOW TO FIND WHO AND WHAT SQL IS USING TEMP TABLESPACE?
Queries, Tablespaces
A query to know which sessions are currently using temporary space in the Oracle Database, and how much space each session consumes.
GOAL
A query to find out user sessions that are using temporary tablespace.
SOLUTION
With the DBT_TEMPUSAGE view, you can point out which user, or session, or SQL is currently using the temporary tablespace.
An example of the information provided by the view
------------------------------------------------------------
-- Victor Khalturin
-- Version 13:27 07-Jan-2022 (001)
--
-- The DBT_TEMPUSAGE displays current information about a Temporary Tablespace usage
--
-- USERNAME - User who requested temporary space
-- SID - Session identifier
-- SERIAL# - Session serial number
-- PID - Operating system process identifier
-- STATUS - Status of the session
-- ELAPSED_SEC - Elapsed time in seconds since the session has changed its status
-- EVENT - Resource or event for which the session is waiting
-- CONNNECTED_FROM - Operating system machine name the connection is established from
-- TABLESPACE - Tablespace in which space is allocated
-- SEGTYPE - Type of sort segment
-- TEMP_USED_GB - Space in GB allocated to the sort
-- PERCENT_USED - Percent(%) used for a tablespace
------------------------------------------------------------
SET PAGES 999
SET LINES 300
COL USERNAME FOR A10
COL SID FOR 99999
COL PID FOR A5
COL STATUS FOR A8
COL SQL_ID FOR A13
COL EVENT FOR A30
COL CONNNECTED_FROM FOR A22
COL TABLESPACE FOR A10
COL SEGTYPE FOR A10
SELECT * FROM DBT_TEMPUSAGE WHERE STATUS='ACTIVE' AND TEMP_USED_GB>0;
USERNAME SID SERIAL# PID STATUS SQL_ID ELA EVENT CONNNECTED_FROM TABLESPACE SEGTYPE TEMP_USED_GB PERCENT_USED
---------- ------ ------- ----- -------- ------------- ---- ---------------------------- --------------- ---------- ------- ------------ ------------
DBSNMP 2907 56461 1191 ACTIVE dx4nqvbtu06bx 551 direct path write temp s001db-ecm01 TEMP02 HASH 95.3 74.5
DBSNMP 3473 29755 20925 ACTIVE 5qqs87rdfucuu 552 control file sequential read s001db-ecm01 TEMP02 HASH 8.9 6.9
Written At
07 JAN 202214:00
Red Hat Release
7.x x64
Database Release
11.2.0.4 - 19.13.0.0.0
Tags In
Share
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Session Tracing
- Sessions
- SQL Trace
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum