【temp绝招】磁盘爆满，更换temp临时表空间存放位置

在数据暴增的时段，数据增长过快，导致磁盘很快爆满，且7*24小时系统，停机时间比较难约
查看了系统，发现temp表空间有200G，有操作空间，执行将temp表空间迁移新盘

1. 查看表空间实际利用率为

SELECT D.tablespace_name,
       SPACE "SUM_SPACE(M)",
       blocks "SUM_BLOCKS",
       used_space "USED_SPACE(M)",
       Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
       SPACE - used_space "FREE_SPACE(M)"
  FROM (SELECT tablespace_name,
               Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
               SUM(blocks) BLOCKS
          FROM dba_temp_files
         GROUP BY tablespace_name) D,
       (SELECT tablespace,
               Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
          FROM v$sort_usage
         GROUP BY tablespace) F
 WHERE D.tablespace_name = F.tablespace(+)
复制

2. 查看用户默认临时表空间及数据文件大小（哈哈~有个200G+）

-- 查看用户默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--查看表空间剩余情况，发现大量剩余，可对其进行操作
select * from dba_temp_free_space 

--查看临时表空间数据存放位置
select round(bytes/1024/1024/1024,2) as bytes,tf.* from dba_temp_files tf

--查看所有临时表空间名字及文件名
select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;  

select f.file#,t.ts#,f.name "File",t.name "Tablespace"，round(f.bytes/1024/1024/1024,2) from v$tempfile f,V$tablespace t where f.ts# = t.ts#;  
 
复制

3. 新建临时表空间（用于替换）

别问为啥要这么多临时表空间文件，业务量实在是大，小了一下就爆了，这个值实际上还不够，后面又加了五六个文件

--原先临时表空间9个文件，200G，按使用情况，先规划四个文件,最大32*5=160G
create temporary tablespace tempnew tempfile '/mnt/vdi1/oradata/tempnew01.dbf' size 10240M autoextend on maxsize 32676M ;
alter tablespace tempnew add tempfile '/mnt/vdi1/oradata/tempnew02.dbf' size 1024M  autoextend on maxsize 32676M ;
alter tablespace tempnew add tempfile '/mnt/vdi1/oradata/tempnew03.dbf' size 10240M autoextend on maxsize 32676M ;
alter tablespace tempnew add tempfile '/mnt/vdi1/oradata/tempnew04.dbf' size 1024M  autoextend on maxsize 32676M ;
alter tablespace tempnew add tempfile '/mnt/vdi1/oradata/tempnew05.dbf' size 512M autoextend on maxsize 32676M; 
复制

4. 切换所有用户默认表空间为新的临时表空间

alter database default temporary tablespace tempnew;

复制

5. 验证是否全部切换

5.1 查看用户表

select distinct temporary_tablespace from dba_users
复制

5.2 查看谁在使用临时表空间

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;

复制

5.3 进一步查询消耗临时表空间的SQL（上一步有查询结果的话）

-------查询消化temp表空间的SQL------------
SELECT S.sid,
       S.serial# sid_serial,
       S.username,
       S.osuser,
       P.spid,
       S.module,
       S.program,
       SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       COUNT(*) sort_ops
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
   AND S.paddr = P.addr
   AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
          S.serial#,
          S.username,
          S.osuser,
          P.spid,
          S.module,
          S.program,
          TBS.block_size,
          T.tablespace
HAVING SUM(blocks) > 10000 -- 80MB
 ORDER BY sid_serial;

 
--将sid代入下面sql查出真正的sql_id
select v$session.sid,ktssosqlid from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial# and v$session.sid in ( 2094,678,1047,767,7); 

-- 根据sql_id 查询执行语句
SELECT S.sid,
       S.serial# sid_serial,
       S.username,
       S.osuser,
       S.module,
       S.program,
       s.machine,
       sa.sql_text,
       sa.sql_fulltext,sa.*

FROM  V$sqlarea sa, v$session s
 where  sa.SQL_ID = s.SQL_ID
 and sa.sql_id in (
'8wzzfttp1971n',
'4bshvwwj3mbfr',
'f3qvudw05zr13',
'f9m81mtm7cbm7')
复制

6. 删除原temp表空间【 谨慎操作 】

6.1 为了加快处理的速度，先删除数据文件

PS：一定是上一步查询完，已经没有用的原temp表空间，再进行删除

-- 按实际情况处理，列举2条
alter tablespace temp drop tempfile '/mnt/oradata/temp02.dbf' ;
alter tablespace temp drop tempfile '/mnt/oradata/temp03.dbf' ;

复制

6.2 最后删除temp表空间

drop tablespace temp including contents and datafiles ;
复制

补充说明：
主要是由于temp表空间被占用导致，可通过上述语句找到使用temp的相关程序，清理或重启程序处理 。（若执行一直无响应大多是被占用导致的）