临时表空间爆满问题

参考：http://blog.itpub.net/30036720/viewspace-2121272/

--查看临时表空间

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;

 

 --查看用户临时表空间

 select d.username, t.file_name, d.temporary_tablespace from
 DBA_TEMP_FILES t,dba_users d where t.tablespace_name = d.temporary_tablespace

 --修改用户的临时表空间

 alter user xxx temporary tablespace data_temp;

 

 

 --查询原先的数据文件做个记录

 select *  from  dba_temp_files

--事务在表空间下面的段状态，必须等待临时表空间下的所有段状态变成offline才能drop

select segment_name,tablespace_name ,r.status
,(initial_extent/1024) initialExtent,(next_extent/1024) nextExtent,

max_extents,v.curext CurExtent from dba_rollback_segs r
,v$rollstat v where r.segment_id=v.USN(+) order by segment_name

 --删除原来的临时表空间

 drop tablespace pdm_temp including contents and datafiles;

 --如果一直删不掉，查看是那些用户仍在使用原先的临时表空间 

select to_char( b.SQL_FULLTEXT),(select osuser from v$session v where v.saddr=session_addr ),

 (select sid from v$session v where v.saddr=session_addr )
, (select serial# from v$session v where v.saddr=session_addr ),

 tablespace,a.*  from  v$tempseg_usage a,v$sql b where a.SQL_ID=b.SQL_ID

 --alter system kill session '428,24107'

 

 --创建新的临时表空间 

create temporary tablespace pdm_temp

tempfile '/data/pdm/DATA/pdm_temp01.dbf' size 30G ,

         '/data/pdm/DATA/pdm_temp02.dbf' size 30G,

         '/data/pdm/DATA/pdm_temp03.dbf' size 30G,

         '/data/pdm/DATA/pdm_temp04.dbf' size 30G,

         '/data/pdm/DATA/pdm_temp05.dbf' size 30G

         autoextend off;

        

 --查看用户临时表空间

 

select d.username, t.file_name, d.temporary_tablespace from DBA_TEMP_FILES t
,dba_users d where t.tablespace_name = d.temporary_tablespace

 --切换为原先的临时表空间

 

   alter user xxx temporary tablespace pdm_temp;

 --删掉临时用来切换的表空间

  drop tablespace data_temp including contents and datafiles;