处理临时表空间使用率过高

select * from dba_TEMP_files
ALTER TABLESPACE TEMP ADD
TEMPFILE '+SYSTEM01' SIZE 30720M;

SELECT DBMS_LOB.substr(DBMS_METADATA.get_ddl('TABLESPACE','DT_WBS_DAT') ) FROM DBA_TABLESPACES ；

---关注下表空间使用率和ALERT中是否有temp的报错，有没有enq: TS的等待事件，以及当前每个会话的temp使用情况
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program
FROM v$session a,
v$sort_usage b,
v$parameter p WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Select se.username, se.sid,su.extents,su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,segtype,sql_text
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 se.username, se.sid;
---------------------
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;
----查找消耗临时表空间资源比较多的SQL语句
SELECT se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
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 se.username, se.sid;
----查看临时表空间的使用情况（GV_$TEMP_SPACE_HEADER 视图必须在 sys 用户下才能查询）
GV_$TEMP_SPACE_HEADER 视图记录了临时表空间的使用大小与未使用的大小
dba_temp_files 视图的 bytes 字段记录的是临时表空间的总大小

SELECT temp_used.tablespace_name,
total - used as"Free",
total as "Total",
round(nvl(total -used, 0) * 100 total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes)/1024/1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name =temp_total.tablespace_name;

---------查询temp表空间使用率：
SELECT V.INST_ID,
V.SID,
V.SERIAL#,
V.USERNAME,
V.STATUS,
V.ACTION,
V.MACHINE,
V.MODULE,
V.OSUSER,
V.TERMINAL,
V.PROGRAM,
V.SQL_ID,
SU.TABLESPACE,
(SU.BLOCKS *
TO_NUMBER((SELECT RTRIM(VALUE)
FROM V$PARAMETER P
WHERE P.NAME = 'db_block_size'))) / 1024 / 1024 AS SIZE_M,
(SELECT ROUND(SUM(BYTES) / (1024 * 1024), 3) FROM V$TEMPFILE) TEMP_TS_SIZE_M,
ROUND((SU.BLOCKS *
TO_NUMBER((SELECT RTRIM(VALUE)
FROM V$PARAMETER P
WHERE P.NAME = 'db_block_size'))) * 100 /
(SELECT SUM(BYTES)
FROM V$TEMPFILE),
3) C_USED_PERCENT,
SU.SEGTYPE,
(SELECT A.SQL_TEXT
FROM GV$SQLAREA A
WHERE A.SQL_ID = NVL(V.SQL_ID,SU.SQL_ID)
AND A.INST_ID = V.INST_ID
AND ROWNUM = 1) SQL_TEXT,
SU.SEGFILE#,
SU.SEGBLK#,
SU.EXTENTS,
SU.BLOCKS,
SU.SEGRFNO#
FROM GV$SORT_USAGE SU, --GV$TEMPSEG_USAGE
GV$SESSION V
WHERE SU.SESSION_ADDR = V.SADDR
AND SU.INST_ID = V.INST_ID
ORDER BY SU.INST_ID, SU.BLOCKS DESC;

---查询temp表空间使用率：
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(M B)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+)

col username for a10
col machine for a20
col SID_SERIAL_SPID for a20
col program for a10
col segtype for a10
col tablespace for a10
select * from (select se.username,
-- se.PADDR,
se.sid || ',' || se.serial# || ',' || p.spid "SID_SERIAL_SPID",
'alter system kill session '''||sid||','||serial#||''''||'
su.sql_id PREV_SQL_ID,
se.sql_id,
se.machine,
se.program,
su.tablespace,
su.segtype,
-- su.contents,
blocks * 8 / 1024 / 1024 gb
from v$session se, v$sort_usage su, v$process p
where se.saddr = su.session_addr
and p.ADDR = se.paddr
order by gb desc)
where rownum < 20;

---
4、查找消耗资源比较多的 SQL 语句

Select se.username,
se.sid,
su.extents,
su.blocks *to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
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 se.username, se.sid;

5、查看当前临时表空间使用大小与正在占用临时表空间的 SQL 语句
select sess.SID, segtype, blocks * 8 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;

--查询实时使用temp表空间的sql_id和sid：
set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
sid,
se.serial#,
se.sql_id,p.SPID,
machine,
p.program,
tablespace,
segtype,
(su.BLOCKS*8/1024/1024) GB
FROM v$session se, v$sort_usage su,v$process p
WHERE se.saddr = su.session_addr
and p.ADDR=se.PADDR
order by su.BLOCKS desc;

/*需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的*/

--- 查询历史的temp表空间的使用的SQL_ID
select a.SQL_ID,
a.SAMPLE_TIME,
a.program,
sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
from v$active_session_history a
where TEMP_SPACE_ALLOCATED is not null
and sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
order by 2 asc,4 desc;


------------

sELECT d.tablespace_name "Name",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
NVL(a.bytes / 1024 / 1024, 0) - NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Free (M)",
d.contents "Type",
d.extent_management "Extent Management"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
AND TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') > TO_CHAR(90,'990.00')
UNION ALL
SELECT d.tablespace_name "Name",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
NVL(t.bytes, 0) / 1024 / 1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %",
NVL(a.bytes / 1024 / 1024, 0) - NVL(t.bytes, 0) / 1024 / 1024 "Free (M)",
d.contents "Type",
d.extent_management "Extent Management"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
AND TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') > TO_CHAR(90,'990.00')
order by 4 desc;