Oracle Temp 表空间切换

 版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/xuzhenxiang/article/details/46883671 一.TEMP表空间作用  临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当 oracle 里需要用到 sort 的时候， PGA 中 sort_area_size 大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间 , 正常来说，在完成 Select 语句、create index 等一些使用 TEMP 表空间的排序操作后， Oracle 是会自动释放掉临时段的。注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。所以 Temp 表空间可能会越来越大。 排序是很耗资源的， Temp 表空间满了 , 关键是优化你的语句，尽量使排序减少才是上策 . 总结为: 临时表空间的主要作用：   索引create或rebuild；   Order by 或 group by；   Distinct 操作；   Union 或 intersect 或 minus；   Sort-merge joins；   analyze. 二、oracle temp表空间切换 2.1 查询TEMP表空间使用情况: 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; 数据库默认表空间: SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE   2    FROM DATABASE_PROPERTIES   3   WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'; 2.2 新建Temp2表空间 create temporary tablespace temp1 tempfile   'D:\APP\ADMINISTRATOR\ORADATA\SDXJ\TEMP2.dbf' size 20M autoextend on next 1M maxsize unlimited tablespace group '' extent management local uniform size 1M; 2.3 修改数据库默认表空间为Temp1 SQL> alter database default temporary tablespace temp1;   数据库已更改。 2.4 删除原来表空间 退出session，删除原表空间 DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; 删除完成。 三、Temp 表空间一些常用查询 3.1、更改系统的默认临时表空间：       --查询默认临时表空间     select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';      --修改默认临时表空间      alter database default temporary tablespace temp1;       --所有用户的默认临时表空间都将切换为新的临时表空间：      select username,temporary_tablespace,default_ from dba_users;       --更改某一用户的临时表空间：      alter user scott temporary tablespace temp; 3.2 查找消耗资源比较的sql语句 /* Formatted on 2015/7/14 21:58:17 (QP5 v5.163.1008.3004) */   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.3、查看当前临时表空间使用大小与正在占用临时表空间的sql语句 select sess.SID, segtype, blocks * 8 / 1000 "MB", 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; 4.4 对临时表空间进行shrink（11g新增的功能） --将temp表空间收缩为20M alter tablespace temp1 shrink space keep 20M; --自动将表空间的临时文件缩小到最小可能的大小 ALTER TABLESPACE temp1 SHRINK TEMPFILE ’.../temp01.dbf’; 总结: 至此Temp表空间维护完成。 --------------------- 作者：奋斗的小鸟_oracle 来源：CSDN 原文：https://blog.csdn.net/xuliq/article/details/46883671 版权声明：本文为博主原创文章，转载请附上博文链接！