释放临时表空间
Oracle临时表空间主要用来做查询和存放一些缓冲区数据，一般用于排序、连接、分组等操作。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到表空间满影响业务。
数据库temp临时表空间增大，一般在数据安装时设置临时表空间大小，默认的情况下都是设置为自动增长。这样会引起一个问题：在数据库使用中temp表空间会自动扩展的越来越大，造成表空间满影响业务。
在执行查询和排序等操作后，Oracle会自动释放已使用的临时段空间。但是，有时候可能会出现临时段空间没有被正确释放的情况。
过大的临时表空间会导致磁盘空间的浪费，并且可能影响数据库性能。因此，及时收缩临时表空间是维护数据库性能和空间利用率的重要步骤，收缩后，使用百分比应该有所下降。
使用百分比较高（大于80%），则表示临时表空间存在较多的空间浪费，需要收缩。
引起临时表空间增大主要使用在以下几种情况：
- order by or group by (disc sort占主要部分);
- 索引的创建和重创建;
- distinct操作;
- union & intersect & minus sort-merge joins;
- Analyze 操作;
- 有些异常也会引起TEMP的暴涨。
常见报错
ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段
收缩表空间
通过SHRINK SPACE选项释放空闲空间
查询临时表空间使用率
用SQL语句检查临时表空间的使用情况，包括总大小、已分配空间和空闲空间。例如：
--查询临时表空间使用率
SELECT
tablespace_name as 表空间,
round((sum_alloc - nvl(sum_free,0))/1024/1024,1) as 已用空间M,
round(sum_max/1024/1024,1) as 总大小M,
round(100*(sum_alloc - nvl(sum_free,0))/sum_max,1) As 使用百分比
FROM ( SELECT tablespace_name, sum(bytes) AS sum_alloc, sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max
FROM dba_temp_files
GROUP BY tablespace_name),( SELECT tablespace_name AS fs_ts_name, sum(bytes) AS sum_free
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name(+)
order by 使用百分比 desc;确定要释放的临时表空间名称
明确要释放的临时表空间的名称。假设临时表空间名称为ENTSERVICETEMP
--查看默认表空间
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--查看用户 和现场负责人确定哪些是业务用户，哪些是需要迁的用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in ('SYS','SYSTEM','HR','OUTLN','MGMT_VIEW','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
ENTSERVICE ENTSERVICE ENTSERVICETEMP查询临时表空间状态
重点关注file_size和autoextensible
select tablespace_name,file_name,bytes/1024/1024 file_size,MAXBYTES/1024/1024 max_size,autoextensible from dba_temp_files;解决办法一：重启实例
实例重启时，smon进程会完成临时段释放、temp表空间的清理操作，不过很多的时侯库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。
解决办法二：增大临时文件大小
数据文件最大为32GB，如果现有数据文件已达到32GB将无法resize成更大值，只能缩小。
alter database tempfile '/oradata/dxj/temp02.dbf' resize 32767mm;解决办法三：将临时数据文件设为自动扩展
alter database tempfile '/oradata/dxj/temp01.dbf' autoextend on next 5m maxsize unlimited; 解决办法四：收缩表空间
--查看一下谁在用临时段
SELECT se.username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr;
SELECT se.username,
sid,
serial#,
se.sql_id,
sa.sql_fulltext,
machine,
program,
tablespace,
segtype,
contents
FROM
v$sort_usage su inner join v$session se on (se.saddr=su.session_addr)
left join v$sqlarea sa on (se.sql_id=sa.sql_id);
--查哪个sql用的临时表空间多
select sess.username,sql.sql_text,sort1.blocks from v$session sess,v$sqlarea sql,v$sort_usage sort1 where sess.serial#=sort1.session_num and sort1.sqladdr=sql.address and sort1.sqlhash=sql.hash_value and sort1.blocks>200;
--杀掉会话
alter system kill session 'sid,serial#' immediate;
场景1：收缩整个表空间
--收缩表空间 其中KEEP {nnnn} M 可省略
语法：alter tablespace temp shrink space KEEP {nnnn} M;
--把temp表空间收缩一下
收缩步骤：alter tablespace temp shrink space;
参数说明：
tablespace_name是临时表空间的名称，{nnnn}是指定要保留的M字节空间。这将释放临时段空间中不再使用的空间。
场景2：收缩指定表空间中的某个数据文件 KEEP {nnnn} M可省略
alter tablespace temp SHRINK TEMPFILE '/oradata/dxj/temp02.dbf' KEEP {nnnn} M;
建议：alter tablespace temp SHRINK TEMPFILE '/oradata/dxj/temp02.dbf'; 解决办法五：向临时表空间中添加数据文件
alter tablespace temp add tempfile '/oradata/dxj/temp02.dbf' size 1G autoextend on next 5m maxsize unlimited; 解决办法六：创建临时表空间组
创建临时表空间组
--创建临时表空间组
create temporary tablespace temp11 tempfile '/oradata/dxj/temp11_01.dbf' size 2M tablespace group group1;
create temporary tablespace temp12 tempfile '/oradata/dxj/temp12_01.dbf' size 2M tablespace group group2; 查询临时表空间组
select * from dba_tablespace_groups; 将表空间从一个临时表空间组移动到另外一个临时表空间组
alter tablespace temp11 tablespace group group2 ;
select * from dba_tablespace_groups;把临时表空间组指定给用户
alter user scott temporary tablespace GROUP2; 更改数据库级默认临时表空间
alter database <db_name> default temporary tablespace GROUP2; 删除临时表空间组 (删除组成临时表空间组的所有临时表空间)
drop tablespace temp11 including contents and datafiles;
select * from dba_tablespace_groups; 扩展：V$SORT_USAGE和V$TEMPSEG_USAGE视图
从Oracle9i开始，v$sort_usage视图从文档中被移除了，取而代之的是v$tempseg_usage，这一改变是因为SORT一词可能会引起误解，虽然排序操作是使用临时段的主要操作，但是除了排序之外，很多其他操作也会适用临时段，所以v$tempseg_usage来的更为确切些。
在v$sort_usage/v$tempseg_usage中有SEGTYPE这样一个字段，该字段代表使用临时段的操作类型，这些不同的操作包括：SORT 、HASH 、DATA 、INDEX 、LOB_DATA 、LOB_INDEX 等，可见v$tempseg_usage这个名字更符合这个视图记录的内容。
查询dba_objects视图，发现原来v$tempseg_usage是一个同义词：
select object_type from dba_objects where object_name='V$TEMPSEG_USAGE';
OBJECT_TYPE
-------------------
SYNONYM再追本溯源原来V$TEMPSEG_USAGE是V_$SORT_USAGE的同义词，也就是和V$SORT_USAGE同源：
col db_link for a30
select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';再进一步，可以找到V_$SORT_USAGE这个视图的底层表以及原始创建语句：
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SORT_USAGE';
VIEW_DEFINITION
---------------
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'), decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#注意：V$TEMPSEG_USAGE或V_$SORT_USAGE视图中的SQLADDR和SQLHASH来自v$session视图中的prev_sql_addr,prev_hash_value，所以和其他视图关联查询时我们需要注意。
参考链接：https://www.cnblogs.com/zhm1985/p/13207071.html
