临时表空间满了

1、环境：linux 、oracle rac、asm共享存储

2、描述：由于数据库用户使用的是oralce默认的临时表空间，不知何种原因，temp临时表空间被沾满了，导致sql查询非常的缓慢

3、日志：Caused by: java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

    at com.yucheng.cmis.dao.SqlClient.queryCount(SqlClient.java:569) ~[cmis-dao-V1.7.3.20170912.RC.jar:?]
    at com.yucheng.cmis.ciis.indivquery.indivsinglequeryapp.component.IndCreditRepInfoAppComponent.queryIndCreditRepInfoAppForSPList(IndCreditRepInfoAppComponent.java:88) ~[IndCreditRepInfoAppComponent.class:?]
    ... 29 more

4、难点：

           （1）不熟悉asm,以至于不知道临时表空间的路径在哪儿

           （2）无法排除是系统本身的sql存在问题，导致表空间被占满

  5、解决方案：

   （1）查看临时表空间信息

     执行“select * from dba_temp_files”

  （2）查询哪些用户在执行

执行“select  a.username,a.sql_id,a.segtype,b.bytes_used/1024/1024/1024||'G',b.bytes_free/1024/1024/1024 from v$tempseg_usage a join v$temp_space_header b on a.tablespace=b.tablespace_name”

解释    username  正在执行sql的用户名 

            sql_id    正在执行的sql的的sql_id 

            segtype   正在执行的SQL语句做的是什么操作 

            BYTES_USED 正在执行sql语句使用的临时表空间的大小

            BYTES_FREE  剩余多少临时表空间

    排除是系统本身的sql存在问题后，为数据库用户新建临时表空间，不使用默认的表空间（临时表空间释放需要重启数据库）

(3)新建表空间（asm中创建临时表空间的路径tempfile '+DATA/ZXJGUAT_TEMP.dbf' ）

create temporary tablespace ZXJGUAT_TEMP 
tempfile '+DATA' 
size 1024m  
autoextend on  
next 50m maxsize unlimited  
extent management local;

（4）更改用户的临时表空间

alter user ciis temporary tablespace ZXJGUAT_TEMP

 

说明：Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间