To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries.<\/p>\n
\r\nSELECT ASH.inst_id,\r\n ASH.user_id,\r\n ASH.session_id sid,\r\n ASH.session_serial# serial#,\r\n ASH.sql_id,\r\n ASH.sql_exec_id,\r\n ASH.sql_opname,\r\n ASH.module,\r\n MIN(sample_time) sql_start_time,\r\n MAX(sample_time) sql_end_time,\r\n ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,\r\n ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,\r\n MAX(temp_space_allocated)\/(1024*1024) max_temp_mb\r\nFROM gv$active_session_history ASH\r\nWHERE ASH.session_type = 'FOREGROUND'\r\nAND ASH.sql_id IS NOT NULL\r\n--AND sample_time BETWEEN to_timestamp('01-04-2019 10:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('13-04-2019 12:00', 'DD-MM-YYYY HH24:MI')\r\nAND sample_time > systimestamp - 3\r\nGROUP BY ASH.inst_id,\r\n ASH.user_id,\r\n ASH.session_id,\r\n ASH.session_serial#,\r\n ASH.sql_id,\r\n ASH.sql_opname,\r\n ASH.sql_exec_id,\r\n ASH.module\r\nHAVING MAX(temp_space_allocated) > 10000\r\norder by 9 desc\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Using ASH to Monitor Temporary Tablespace Usage To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries. SELECT ASH.inst_id, ASH.user_id, ASH.session_id sid, ASH.session_serial# serial#, ASH.sql_id, ASH.sql_exec_id, … Continue reading