临时表空间使用过大排查

1.查看哪些sql语句占用的临时表空间空间最多。

select ash.TEMP_SPACE_ALLOCATED / 1024 / 1024 / 1024 G,
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.TOP_LEVEL_SQL_ID,
ash.PLSQL_ENTRY_OBJECT_ID,
ash.PLSQL_OBJECT_ID,
ash.MODULE,
ash.MACHINE
from gv$active_session_history ash
where ash.SESSION_TYPE = 'FOREGROUND'
and ash.TEMP_SPACE_ALLOCATED is not null
--and ash.PLSQL_ENTRY_OBJECT_ID is null
--and ash.PLSQL_OBJECT_ID is null
--and ash.SQL_ID=ash.TOP_LEVEL_SQL_ID
order by 1 desc

2.<alter database tempfile 2 drop; ><alter tablespace temp drop tempfile 2;>drop临时文件报错：ORA-25152: TEMPFILE cannot be dropped at this time ，原因临时表被占用

SELECT se.INST_ID,
se.username,
se.schemaname,
se.MACHINE,
se.MODULE,
su.TABLESPACE,
'ALTER SYSTEM KILL SESSION ' || '''' || se.sid || ',' || se.serial# || ',' || '@' ||
se.inst_id || '''' || ' immediate;' kill_stat
FROM gv$session se, gv$sort_usage su
WHERE se.saddr = su.session_addr
and se.INST_ID = su.INST_ID
and su.tablespace = '<tablespace_name>';

 

转载于:https://www.cnblogs.com/erwadba/p/8041332.html