HOW TO FIND WHO AND WHAT SQL IS USING TEMP TABLESPACE?
Database Performance, Queries, Standard Queries, Temporary Tablespace
0
REQUEST
I need details about what exactly is using the space in a Temporary Tablespace.
SOLUTION
With the TEMP_USAGE view you can point out which a user, a session or a sql_id is currently using your temp space.
CREATE OR REPLACE VIEW TEMP_USAGE AS
------------------------------------------------------------
-- Victor Khalturin
-- Version 13:25 15.07.2020 (002)
--
-- The TEMP_USAGE 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
------------------------------------------------------------
WITH TEMP_TABLESPACE_SIZE AS (
SELECT TABLESPACE_NAME,
SUM(BYTES) MAX_BYTES
FROM
DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME
)
SELECT NVL(S.USERNAME, 'BACKGROUND_PROCESS') USERNAME,
S.SID,
S.SERIAL#,
P.SPID PID,
S.STATUS,
S.SQL_ID,
S.LAST_CALL_ET ELAPSED_SEC,
S.EVENT,
S.MACHINE CONNNECTED_FROM,
TU.TABLESPACE,
TU.SEGTYPE,
ROUND(TU.BLOCKS*TS.BLOCK_SIZE/1024/1024/1024,1) TEMP_USED_GB,
ROUND(TU.BLOCKS*TS.BLOCK_SIZE/TTS.MAX_BYTES*100,1) PERCENT_USED
FROM
V$SESSION S,
V$PROCESS P,
V$TEMPSEG_USAGE TU,
DBA_TABLESPACES TS,
TEMP_TABLESPACE_SIZE TTS
WHERE
S.PADDR=P.ADDR AND
S.SADDR=TU.SESSION_ADDR AND
TU.TABLESPACE=TS.TABLESPACE_NAME AND TS.CONTENTS='TEMPORARY' AND
TU.TABLESPACE=TTS.TABLESPACE_NAME;
COMMENT ON TABLE TEMP_USAGE IS '------------------------------------------------------------
-- Victor Khalturin
-- Version 13:25 15.07.2020 (002)
--
-- The TEMP_USAGE 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
------------------------------------------------------------'
------------------------------------------------------------
-- Victor Khalturin
-- Version 13:25 15.07.2020 (002)
------------------------------------------------------------
Query the view
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 TEMP_USAGE 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
Here the session 2907 with sql_id dx4nqvbtu06bx has used 95.3GB (74.5%) of all avalable space in the TEMP02 tablespace.
if you need to know a sql text of the dx4nqvbtu06bx you should query the v$sql view
SET PAGES 999
SET LINES 300
SET LONG 20000000
COL SQL_ID FOR A13
COL SQL_FULLTEXT FOR A100
SELECT SQL_ID, SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='dx4nqvbtu06bx';
SQL_ID SQL_FULLTEXT
------------- ----------------------------------------------------------------------------------------------------
dx4nqvbtu06bx SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND
SESSION_STAMP=:B1 AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1
Version : 18:46 08.05.2020
Oracle Database: 11.2.0.4
Oracle Database: 11.2.0.4
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Certificates
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Cipher Suites (1)
- Database Errors (5)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum