(17)管好临时表空间

临时表空间的管理是个不大不小的事，如果你不理它，你会发现它好像忽然间就上几十G了，而且还在增长，弄得磁盘空间很紧张。而且你扩容后，还是会发现使用率经常是100%.所以有必要说一下这种表空间的一些事情。 从几方面说: 一. 临时表空间的用途 二. 常出现的问题及处理方法 三. 一些建议及好用的特性，如临时表空间组

(一)临时表空间的用途 临时表空间主要用于排序和缓存临时的数据。下面列几个常用的. 1. 排序、分组 order by or group by 2. distinct 3. 集合运算 union & intersect & minus 4. Analyze操作 5. 还一种是发生异常时，也会放入临时表空间，让其暴涨。 上面这些，大部份都是比较大型的操作，在内存在完成不了，所以会放到临时表空间中。而临时表空间比较特殊，它使用的临时段在被分配使用后，并不能被其它用户或查询重用，此空间不再被回收，造成只能不断的使用新空间。而上面列得情况中，像排序之类是经常性的，所以空间占用涨得很快。不过它在数据库关闭重启后是能自动释放的。但哪能经常重启库，所以不实用。

(二)常出现的问题及处理方法 1. 现在的临时表空间不够了，而自动扩展没有开。会报ORA-1652 unable to extend temp segment 这里有个小问题，有人可能会问，我已经开得很大了，怎么还不够呢？ 原因是，很多时候一个临时表空间不是一个sql再用，而是用多个，其中一个大的点了99%,另一个sql执行就会报这个错了。 已经报ORA-1652的情况下，11g以前的方法是，扩容原来的表空间或联机删除并重新创建临时表，11g以后能用收缩表空间的方式来做。 方法一: 扩容
a. 查出现有临时表信息 select t.bytes/1024/1024 as "total(MB)", t.* from dba_temp_files t ; b. 重置大小 alter database tempfile 'C:/ORACLE/ORADATA/XCLDB/TEMP01.DBF'resize 2G;

方法二: 删除并重新创建临时表空间 a. 查出现有临时表信息 select t.bytes/1024/1024 as "total(MB)",
t.user_bytes/1024/1024 as "used(MB)",
t.* from dba_temp_files t;
b. 创建一个新的临时表空间 create temporary tablespace TMP02_USR empfile 'C:/ORACLE/ORADATA/XCLDB/TMP02_USR .DBF' size 10m reuse autoextend on next 1m maxsize unlimited; c. 将用户的临时表空间换为这个新的 alter user 用户名 temporary tablespace TMP02_USR; --如果你使用的系统默认的临时表空间，则要用下面的命令更换表空间 alter database default temporary tablespace TMP02_USR; d.连文件删除原来的临时表空间 drop tablespace 表空间名 including contents and datafiles cascade constraints; 小技巧: 如果原来的这个临时表空间还有用户在使用，是删除不了的，这时可以通过下面SQL查出来还有谁在使用:
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
  and tablespace= '原来的临时表空间名';再用 alter system kill session 'sid,serial@inst#';去kill掉，再执行drop即可。

再附上删除某一个数据文件的命令在这:
alter database tempfile '文件全路径" drop;
e. 再通过 dba_users 检查下是否成功了。
select username,temporary_tablespace from dba_users;

方法三: 收缩表空间 -- 将其收缩到100m大小
alter tablespace 临时表空间名 shrink space keep 100m; --也可以回收表空间所有的空间 alter tablespace 临时表空间名 shrink space;

查看结果: select * from dba_temp_free_space; 2. 临时表空间开了自动扩展,无限制的扩展，造成资源紧张。 关掉自动扩展是个解决方法，但这也有个问题，如果关了自动扩展，那多大合适，会不会出上不够用得情况？ 这个其实也只能靠估算业务高峰期的峰值来算。我也没想到很合理的算法。而这个估算不合理或突然碰到暴涨 的情况，关掉自动扩展也会很被动，会报错。 所以建议的是平常要盯紧点，在估计磁盘空间还有足够的情况下 可以打开，但磁盘资源相对比较紧张了，则可以关掉这个选项。

(三) 一些建议及好用的特性，如临时表空间组 建议1. 查出消耗资源比较大的sql语句然后对其进行优化，这是治本的方法。
 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 平时注意监控，在新建临时表空间时，如果估计磁盘空间还有足够的情况下可以打开自动扩展，但磁盘资源相对比较紧张了，则可以关掉这个选项

建议3. 不要所有用户都用系统默认的临时表空间，应当分开。 建议4. 使用临时表空间组，能比较好的解决问题。 表空间组能在空间不足时，向里面删除和添加临时表空间文件。还能让用户的不同session使用不同的表空间文件，实际上就是实现了IO的负载均衡。所以这个推荐使用。 先做个例子:

a. 生成临时表空间组
 create temporary tablespace  TMPG1_01 tempfile 'C:/ORACLE/ORADATA/XCLDB/ TMPG1_01 .DBF' size 10m tablespace group tmp_grp1;
 create temporary tablespace  TMPG1_02 tempfile 'C:/ORACLE/ORADATA/XCLDB/ TMPG1_02 .DBF' size 10m tablespace group tmp_grp1;
 create temporary tablespace  TMPG1_03 tempfile 'C:/ORACLE/ORADATA/XCLDB/ TMPG1_03 .DBF' size 10m tablespace group tmp_grp1;b. 查询是否成功了 select * from dba_tablespace_groups; c. 指定用户使用此表空间组 alter user demo temporary tablespace TMP_GRP1; d. 查看是否alter成功 select username,temporary_tablespace from dba_users; 临时表空间组也可以删除组成员，命令是一样的: drop tablespace TMPG1_01 including contents and datafiles cascade constraints;

临时表空间管理就写得这了。好多。