查看占用排序的回话

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_history

temp_space_allocated

sample_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')