临时表空间满的解决办法

 
1 创建中间临时表空间：
 create temporary tablespace temp1 tempfile '+DG1/rac/tempfile/temp120130605' size 10M;

alter database default temporary tablespace temp1;

select username,temporary_tablespace from dba_users;    ----------查询用户的默认临时表空间
2 删除临时表空间：
drop tablespace temp including contents and datafiles;

3 重建临时表空间
create temporary tablespace temp tempfile '/dev/rrac_temp' size 30710M;
alter tablespace temp add tempfile '/dev/rrac_temp01' size 30710M;
alter tablespace temp add tempfile '/dev/rrac_temp02' size 30710M;
alter tablespace temp add tempfile '/dev/rrac_temp03' size 30710M;
alter tablespace temp add tempfile '/dev/rrac_temp04' size 30710M;
alter database default temporary tablespace temp;
select username,temporary_tablespace from dba_users;

4 删除中转临时表空间
drop tablespace temp1 including contents and datafiles;

alter database datafile '/dev/rlvsp_data02' resize 110G;

----------------------------------------------重建临时表空间过程中用的sql语句--------------------------------------------------

1、哪些sql语句在使用temp表空间：
select sess.SID, segtype, blocks*8/1000 ,sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;

2、找出最消耗资源的语句（查询特慢，很消耗资源）：
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;
 
3、temp表空间的使用情况：
  SELECT temp_used.tablespace_name,used,
       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;
 
 ----------------------------查找当前哪些会话在使用临时表空间，可以kill--------------------------
 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 ;