临时表空间满或者丢失

临时表空间主要使用在以下几种情况：
1、order by or group by (disc sort占主要部分)；
2、索引的创建和重创建；
3、distinct操作；
4、union & intersect & minus sort-merge joins；
5、Analyze 操作；
6、有些异常也会引起TEMP的暴涨。
Oracle临时表空间暴涨的现象经过分析可能是以下几个方面的原因造成的：
1. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。
2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。
3.对查询的某些字段没有建立索引。Oracle中，如果表没有索引，那么会将所有的数据都复制到临时表空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。
针对以上的分析，对查询的语句和索引进行了优化，情况得到缓解，但是需要进一步测试。

需要注意的是，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

如果临时表空间是temporary的，空间不会释放，只是在sort结束后被标记为free的，如果是permanent的，由SMON负责在sort结束后释放，都不用去手工释放的

当临时表空间撑满后，就会影响到系统性能，这时我们就需要人为干涉来适当释放表空间大小。

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP 

 

1、查询sql

1、查询临时表空间占用情况（最大使用，不是真实）
select * from (select c.tablespace_name,sum(decode(c.maxbytes, 0, c.bytes, maxbytes))/1024/1024 max_mb,sum(c.bytes) /1024 /1024 temp_mb from dba_temp_files c group by tablespace_name) b;

2、查看临时表空间文件
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

3、查询用户默认临时表空间
select username,temporary_tablespace from dba_users;

4、查看数据库默认的临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

5、查看哪些用户在使用临时段
select se.username,se.sid,se.serial#,se.sql_address,se.machine,se.program,su.tablespace,su.segtype,su.contents from v$session se, v$sort_usage su where se.saddr = su.session_addr;

SELECT s.sid,s.serial#,s.username,p.spid,s.module,p.program,SUM(su.blocks)*tbsp.block_size/1024/1024 mb_used ,su.tablespace FROM v$sort_usage su,v$session s,dba_tablespaces tbsp,v$process p WHERE su.session_addr=s.saddr AND su.tablespace=tbsp.tablespace_name AND s.paddr=p.addr GROUP BY s.sid,s.serial#,s.username,s.osuser,p.spid,s.module, p.program,tbsp.block_size,su.tablespace ORDER BY s.sid; 

6、临时表空间数据文件使用率（真实占用）
select round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) "total MB",round(((f.BYTES_FREE + f.BYTES_USED) - nvl(p.BYTES_USED, 0)) / 1024 / 1024,2) "Free MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) "Used MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) /round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) * 100 || '%' "UPRC",d.file_name "fileName" from sys.v_$temp_space_header f,Dba_Temp_Files d,SYS.v_$temp_Extent_Pool p where f.TABLESPACE_NAME(+) = d.tablespace_name and f.FILE_ID(+) = d.file_id and p.FILE_ID(+) = d.file_id and d.tablespace_name = 'TEMP';

SELECT sum(blocks)*8 FROM v$tempseg_usage WHERE tablespace = 'temp';

7、查询在sort排序区使用的执行耗时的用户SQL
select * from (select sess.sid,segtype,blocks,sql_text from v$sort_usage su,v$session sess,v$sql sql where su.session_addr = sess.saddr and sql.address = sess.sql_address order by blocks desc) where rownum <= 5;

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;

2、收缩临时表空间

一、shrink收缩表空间（11g新增功能，支持在线收缩）
---将temp表空间收缩为20M
alter tablespace temp shrink space keep 20M; 
---自动将表空间的临时文件缩小到最小可能的大小
alter tablespace temp shrink  tempfile  '/u01/app/oracle/data/temp02.dbf'
或者直接shink
alter tablespace temp shrink;

或者alter database tempfile 'd:powerdmsdb\dmsdb\temp01.dbf' resize 1024M;

二、temp临时表空间添加数据文件
alter tablespace "TEMP" add tempfile '+data/ora/tempfile/temp01.dbf' size 800m;

三、重建临时表空间
由于oracle默认的临时表空间是不能直接drop的，所以我们需要通过创建中转临时表空间的方法来实现间接删除重建临时表空间的目的。
---首先查看现有temp临时表的名字及路径
select name from v$tempfile;
---查看哪些用户使用临时表空间
select username,temporary_tablespace from dba_users;

1.创建中转表空间
create temporary tablespace  temp01 tempfile '/u01/app/oracle/oradata/tempfile/temp01.dbf' size 512M reuse autoextend on next 1M maxsize 2G;(临时表空间不建议设置成无限增长大小，建议设置一个固定最大值) 
2.改变默认临时表空间为temp01
alter database default  temporary tablespace  temp01;
3.确认默认表空间是否为新创建的temp01
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
4.查询出正在使用temp表空间的会话进程并删除掉。
select se.username,se.sid,se.serial#,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;

SQL>alter system kill session 'sid,serial#';
5.删除原来临时表空间
drop tablespace temp including contents and datafiles;
6.重新创建temp临时表空间
create temporary tablespace  temp tempfile '/u01/app/oracle/oradata/tempfile/temp.dbf' size 512M reuse autoextend on next 100M maxsize 3g;
7.重置默认临时表空间为temp
alter database default  temporary tablespace  temp;
 8.删除中转用临时表空间
drop tablespace temp01 including contents and datafiles;
 9.如果有必要，重新指定用户表空间为重建的临时表空间
alter user test temporary tablespace  temp; 

四、重启数据库

五、优化占用temp最大的SQL语句。
---从11g开始，ash视图新增TEMP_SPACE_ALLOCATED列，能看到不同时间段的增长情况。

select to_char(sample_time,'yyyymmdd hh24:mi:ss'),session_id,session_serial#,sql_id,max(TEMP_SPACE_ALLOCATED/1024/1024)
from v$active_session_history where con_id=3
and to_char(sample_time,'yyyymmdd hh24')='20200204 16'
group by to_char(sample_time,'yyyymmdd hh24:mi:ss'),session_id,session_serial#,sql_id order by 5;

测试环境资源有限，最后一列就是占用的临时表空间大小，可以看到不同时间段temp占用情况。实际生产中，可以看到几分钟内，某条sql_id占用急速上升。
查看sql_id内容：
select * from table(dbms_xplan.display_cursor('&sqlid'));

3、临时表空间数据文件丢失

1、alter  tablespace temp add tempfile 'e:\sysoradata\temp02.dbf' size 20m;
2、alter tablespace temp drop tempfile 'e:\sysoradata\temp01.dbf';
3、select  f.file#,t.ts#,f.name"file",t.name "tablespace" from v$tempfile f,v$tablespace t where  f.ts#=t.ts#;
4、 select  tablespace_name,status,contents,extent_management from dba_tablespaces;