Oracle临时表空间占用情况分析及解决

最近有两个项目点反馈临时表空间出现快速增长，导致系统硬盘被占满的情况。原来项目较紧张，采取定时清空临时表空间的策略暂时支撑，最近能抽出时间分析下具体原因：
首先了解下临时表空间的作用：
Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。
临时表空间的主要作用：索引create或rebuild、Order by 或 group by、Distinct 操作、Union 或 intersect 或 minus、Sort-merge joins、analyze
oracle表空间的类型：
1、永久性表空间：一般保存表、视图、过程和索引等的数据。
2、临时性表空间：只用于保存系统中短期活动的数据。
3、撤销表空间：用来帮助回退未提交的事务数据。
Oracle表空间的作用：
1、决定数据库实体的空间分配;
2、设置数据库用户的空间份额;
3、控制数据库部分数据的可用容性;
4、分布数据于不同的设备之间以改善性能;
5、备份和恢复数据。

小白知识科普完，接下来查询查询临时表空间的使用情况及占用临时表空间的SQL语句：

–临时表空间文件使用率
select c.tablespace_name,
to_char(c.bytes/1024/1024/1024,‘99,999.999’) total_gb,
to_char( (c.bytes-d.bytes_used)/1024/1024/1024,‘99,999.999’) free_gb,
to_char(d.bytes_used/1024/1024/1024,‘99,999.999’) use_gb,
to_char(d.bytes_used*100/c.bytes,‘99.99’) || '%'use
from (select tablespace_name,sum(bytes) bytes
from dba_temp_files GROUP by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool GROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name;

使用率基本满了

–临时表空间占用SQL
select *
from (select t.sample_time,
s.PARSING_SCHEMA_NAME,
t.sql_id,
t.sql_child_number as sql_child,
round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ’ G’ as temp_used,
round(t.temp_space_allocated /
(select sum(decode(d.autoextensible, ‘YES’, d.maxbytes, d.bytes))
from dba_temp_files d),
2) * 100 || ’ %’ as temp_pct,
t.program,
t.module,
s.SQL_TEXT
from v$activ{e}_{s}essio{n}_{h}istoryt,v$sql s
where t.sample_time > to_date(‘2020-05-05 22:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)
and t.sample_time < to_date(‘2020-05-15 23:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)
and t.temp_space_allocated is not null
and t.sql_id = s.SQL_ID
order by t.temp_space_allocated desc)
where rownum < 200
order by temp_used desc;

可以查看指定时间段的占用临时表空间较大的SQL.
我这边通过SQL分析的结果有两个点造成临时表空间占用临时表空间较大：
1、物化视图定时更新需要group by。
2、系统有个定时任务，查询全库表字段的定义并按表名称分组。

查到原因后直接优化，问题解决。