temp 表空间使用率较高分析

查看占用排序的回话 col MACHINE for a15 col PROGRAM for a15 col USERNAME for a15 SELECT se.username,         se.sid,         se.serial#,         se.sql_address,         se.machine,         se.program,     se.sql_id,    se.PREV_SQL_ID,        su.tablespace,         su.SEGTYPE,         su.CONTENTS,    su.blocks*8/1024   FROM v$session se,         v$sort_usage su   WHERE se.saddr=su.session_addr    查看排序占用大小  Select su.blocks*8/1024,se.username,se.machine,se.program,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))/1024/1024 as    Space,tablespace,segtype,SQL_FULLTEXT from v$sort_usage su,v$parameter p,v$session se,v$sql s where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by su.extents asc ,se.username,se.sid #查询表空间的情况： SELECT T1.TABLESPACE_NAME,        t1.TOTAL_SIZE_MB,        NVL (t2.used_mb, 0),        ROUND (NVL (t2.used_mb, 0) / t1.TOTAL_SIZE_MB, 2)*100||'%' AS used_percent   FROM    (  SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size_mb                FROM dba_temp_files            GROUP BY TABLESPACE_NAME) t1        LEFT JOIN           (  SELECT tablespace, SUM (blocks) * 8 / 1024 AS used_mb                FROM v$sort_usage            GROUP BY tablespace) t2        ON t1.tablespace_name = t2.tablespace; -----     set pages 200 line 200 wrap off select name,type, to_char(round(total_mb/1024,4),'fm9999990.0000') total_gb, to_char(round(free_mb/1024,4),'fm9999990.0000') free_gb, to_char(round(cold_used_mb/1024,4),'fm9999990.0000') used_gb, to_char(round(free_mb/total_mb*100,2),'fm9999990.0000') free_pct, to_char(round(cold_used_mb/total_mb*100,2),'fm9999990.0000') used_pct from v$asm_diskgroup; SELECT tablespace_name, to_char(round(used_space/1024/1024*8,2),'fm9999990.0000') as "USED_GB", to_char(round((tablespace_size - used_space)/1024/1024*8,2),'fm9999990.0000') as "FREE_GB", to_char(round(tablespace_size/1024/1024*8,2),'fm9999990.0000') as "MAX_GB", to_char(round(used_percent,2),'fm9999990.0000') as "USED_OF_MAX_PCT" FROM DBA_TABLESPACE_USAGE_METRICS  order by round(used_percent,2) desc; dba_hist_active_sess_historytemp_space_allocatedsample_time between to_date('2025-02-17 08:45:00','yyyy-mm-dd hh24:mi:ss')to_char(sample_time,'yyyy-mm-dd hh24:mi:ss')