oracle 查看临时表空间占用情况

select tablespace_name,file_name,bytes/1024/1024/1024 file_size,autoextensible from dba_temp_files;
--------------
SELECT B.TABLESPACE,
B.SEGFILE#,
B.SEGBLK#,
B.BLOCKS,
B.BLOCKS * 8 / 1024 / 1024,
A.SID,
A.SERIAL#,
A.USERNAME,
A.OSUSER,
A.MACHINE,
A.STATUS,
C.SQL_TEXT,
B.CONTENTS,a.LOGON_TIME
FROM V$SESSION A, V$SORT_USAGE B, V$SQL C
WHERE A.SADDR = B.SESSION_ADDR
AND A.SQL_ADDRESS = C.ADDRESS(+)
-- and C.SQL_TEXT like '%表名%'
ORDER BY B.BLOCKS DESC
;
-------------------------------------

---查看时间段内表空间占用情况

select *
from
(
select INSTANCE_NUMBER iid,
to_char(SAMPLE_TIME,'yyyymmdd hh24:mi:ss') SAMPLE_TIME,
to_char(SQL_EXEC_START,'yyyymmdd hh24:mi:ss') SQL_EXEC_START,
SESSION_ID sess,
SESSION_SERIAL# serial#,
machine,
sql_id,
round(TEMP_SPACE_ALLOCATED/1024/1024/1024) GB,
round(TEMP_SPACE_ALLOCATED/1024/1024)-lag(round(TEMP_SPACE_ALLOCATED/1024/1024),1,0) over (partition by INSTANCE_NUMBER,SESSION_ID,SESSION_SERIAL#,machine,program,SQL_EXEC_START order by SAMPLE_TIME) diff
from dba_hist_active_sess_history
where
SAMPLE_TIME between TO_TIMESTAMP ('2022-04-08 12:00:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('2022-04-08 21:00:00', 'YYYY-MM-DD HH24:MI:SS')
--and sql_id ='cuy4bz9jdv9p7'
--and TEMP_SPACE_ALLOCATED >=64424509440
--and event = 'direct path write temp'
order by INSTANCE_NUMBER,SAMPLE_TIME,SQL_EXEC_START
)
where diff >=1 order by GB desc
;

---select * from dba_hist_sqltext a where a.sql_id='3w8xrnxjjc4rt';