Temp Tablespace Analysis in Oracle Tablespace

 Temp Tablespace Analysis in Oracle Tablespace

Temp in Database

SELECT
    tablespace_name,
    SUM(bytes_used) / (1024 * 1024) AS used_mb,
    SUM(bytes_free) / (1024 * 1024) AS free_mb
FROM
    v$temp_space_header
GROUP BY
    tablespace_name;

Temp Tablespace details check

set linesize 190
col SID_SERIAL for a30
col username for a15
col osuser for a15
col module for a15
col program for a30
col tablespace for a20
set pages 5000
SELECT s.sid || ',' || s.serial# AS sid_serial,
       s.username,
       s.osuser,
       p.spid,
       s.module,
       p.program,
       SUM(t.blocks) * tbs.block_size / 1024 / 1024 AS mb_used,
       t.tablespace,
       COUNT(*) AS statements
FROM v$sort_usage t,
     v$session s,
     dba_tablespaces tbs,
     v$process p
WHERE t.session_addr = s.saddr
  AND s.paddr = p.addr
  AND t.tablespace = tbs.tablespace_name
GROUP BY s.sid, s.serial#,
         s.username,
         s.osuser,
         p.spid,
         s.module,
         p.program,
         tbs.block_size,
         t.tablespace
ORDER BY sid_serial;

 

Active Session 

SET LINESIZE 190
COLUMN username FORMAT A30
COLUMN sql_id FORMAT A20
COLUMN tablespace FORMAT A30
COLUMN segtype FORMAT A10
SELECT 
    s.username,
    s.sid,
    s.serial#,
    t.sql_id,
    t.tablespace,
    t.segtype,
    t.blocks
FROM 
    v$session s
JOIN 
    v$tempseg_usage t ON s.saddr = t.session_addr
WHERE 
    s.username IS NOT NULL;
Particular Day
column sum_max_mb format 999,999,999;
column temporary_tablespace format A20
WITH
pivot1 AS
(
SELECT
trunc(ash.sample_time,'MI') sample_time,
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace,
max(temp_space_allocated)/(1024*1024) max_temp_mb
FROM  GV$ACTIVE_SESSION_HISTORY ash, dba_users U
WHERE
ash.user_id = U.user_id
and ash.session_type = 'FOREGROUND'
GROUP BY
trunc(ash.sample_time,'MI'),
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace
)
SELECT  temporary_tablespace, sample_time, sum(max_temp_mb) sum_max_mb
from pivot1 where sample_time like '28-OCT-2024%'
GROUP BY sample_time, temporary_tablespace
ORDER BY temporary_tablespace, sample_time;

set linesize 190
COLUMN module format A25
COLUMN sql_opname format A20
COLUMN etime_secs FORMAT 999,999.9
COLUMN etime_mins FORMAT 999,999.9
COLUMN user_id FORMAT 999999
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 99999
COLUMN username FORMAT A25
COLUMN inst_id FORMAT 99
COLUMN sql_opname FORMAT A10
COLUMN sql_id FORMAT A13
COLUMN sql_exec_id FORMAT 9999999999
COLUMN max_temp_mb FORMAT 999,999,999
COLUMN sql_start_time FORMAT A28
COLUMN sql_end_time FORMAT A28
 
 
SELECT ASH.inst_id,
  ASH.user_id,
  ASH.session_id sid,
  ASH.session_serial# serial#,
  ASH.sql_id,
  ASH.sql_exec_id,
  ASH.sql_opname,
  ASH.module,
  MIN(sample_time) sql_start_time,
  MAX(sample_time) sql_end_time,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,
  MAX(temp_space_allocated)/(1024*1024) max_temp_mb
FROM gv$active_session_history ASH
WHERE ASH.session_type = 'FOREGROUND'
AND ASH.sql_id        IS NOT NULL
AND sample_time BETWEEN to_timestamp('28-10-2024 00:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('29-10-2024 02:00', 'DD-MM-YYYY HH24:MI')
  --and  ASH.sql_id = &SQL_ID
GROUP BY ASH.inst_id,
  ASH.user_id,
  ASH.session_id,
  ASH.session_serial#,
  ASH.sql_id,
  ASH.sql_opname,
  ASH.sql_exec_id,
  ASH.module;