Oracle 11g 临时表空间管理
作者 | JiekeXu
来源 | JiekeXu之路（ID: JiekeXu_IT）
转载请联系授权 | (微信ID：xxq1426321293)
大家好，我是 JiekeXu,很高兴又和大家见面了，今天分享Oracle 11g 临时表空间管理相关内容。本文发布于微信公众号【JiekeXu之路】，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
近期我们运维的数据库有几台出现了 temp 临时表空间使用率过高告警的问题，发现有些 DBA 竟然选择直接添加数据文件或者直接 resize 30G 来消除告警。这样导致临时文件很大占用很多磁盘空间，没有想到优化管理它，临时表空间过大只有重启实例使用率才会下降，如果没有临时表空间实例重启也会自动创建出来，那么今天抽出点时间来说说临时表空间的管理。
一、临时表空间
临时表空间包含仅在会话期间持续存在的临时数据。临时表空间可以提高无法装入内存的多个排序操作的并发性，还可以提高排序期间空间管理操作的效率。在临时表空间中，针对特定实例的所有排序操作共享一个排序段，并且每个执行需要临时空间的排序操作的实例都存在排序段。排序段是由在启动后使用临时表空间进行排序的第一个语句创建的，只有在关闭时才释放。默认情况下，为每个新的 Oracle 数据库安装创建一个名为 TEMP 的临时表空间。也可以使用 create TABLESPACE 语句创建额外的临时表空间。
临时表空间用于存储以下内容:
中间结果排序；
临时表和临时索引；
临时 Lob；
临时 B tree
Oracle 临时表空间作用
Oracle 临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题 SQL 语句的执行，temp 表空间会一直增长直到耗尽磁盘空间。网络上有人猜测在磁盘空间的分配上，oracle 使用的是贪心算法，如果上次磁盘空间消耗达到 1GB，那么临时表空间就是 1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。
临时表空间的主要作用：
索引 create 或 rebuild；
Order by 或 group by；
Distinct 操作；
Union 或 intersect 或 minus；
Sort-merge joins；
analyze.
==========================================
oracle 临时表空间的增删改查等管理操作
1、查看临时表空间 （dba_temp_files 视图）（v_$tempfile 视图）
select tablespace_name,file_name,bytes/1024/1024file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用户查看
2、查看临时表空间当前使用的大小和历史最大使用的大小
select 'max' asstatus,
tablespace_name,
sum(bytes_cached) / 1024 / 1024 as usage
from v$temp_extent_pool
group by tablespace_name
union all
select 'current' as status,
ss.tablespace_name,
sum((ss.used_blocks * ts.blocksize)) / 1024 / 1024 asusage
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;
3、查看临时表空间的使用情况（GV_$TEMP_SPACE_HEADER 视图必须在 sys 用户下才能查询）
GV_$TEMP_SPACE_HEADER 视图记录了临时表空间的使用大小与未使用的大小
dba_temp_files 视图的 bytes 字段记录的是临时表空间的总大小
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 BYtablespace_name) temp_used,
(SELECTtablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BYtablespace_name) temp_total
WHERE temp_used.tablespace_name =temp_total.tablespace_name;
查看临时表空间对应的临时文件的使用情况
SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,
BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED ,
BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDERBY 1 DESC;
4、查找消耗资源比较多的 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;
5、查看当前临时表空间使用大小与正在占用临时表空间的 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;
6、扩展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 100m;
方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;
方法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100m;
7、创建临时表空间：
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp11.dbf' size 10M;
8、更改系统的默认临时表空间：
--查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认临时表空间
alter database default temporary tablespace temp1;
所有用户的默认临时表空间都将切换为新的临时表空间：
select username,temporary_tablespace,default_ fromdba_users;
--更改某一用户的临时表空间：
alter user scott temporary tablespace temp;
9、删除临时表空间
删除临时表空间的一个数据文件：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' drop;
删除临时表空间(彻底删除)：
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;
10、缩小临时表空间大小
alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' resize 100M;
二、临时表空间组
临时表空间组是 ORACLE 10g 引入的一个新特性，它是一个逻辑概念，不需要显示的创建和删除。只要把一个临时表空间分配到一个组中，临时表空间组就自动创建，所有的临时表空间从临时表空间组中移除就自动删除。
一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制.
A temporary tablespace group contains at least one tablespace. There is no limitfor a group to have a maximum number of tablespaces
如果删除一个临时表空间组的所有成员，该组也自动被删除。临时表空间的名字不能与临时表空间组的名字相同。
It shares the namespace of tablespaces, thus its name cannot be the same as thatof any tablespace.
可以在创建临时表空间时指定表空间组，即隐式创建。
1）创建临时表空间组：
create temporary tablespace tempts1 tempfile '/u01/app/oracle/temp1_02.dbf' size 2M tablespace group group1;
create temporary tablespace tempts2 tempfile '/u01/app /oracle/temp2_02.dbf' size 2M tablespace group group2;
2）查询临时表空间组：dba_tablespace_groups 视图
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP1 TEMPTS1
GROUP2 TEMPTS2
3）将表空间从一个临时表空间组移动到另外一个临时表空间组：
alter tablespace tempts1 tablespace group GROUP2 ;
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP2 TEMPTS1
GROUP2 TEMPTS2
4）把临时表空间组指定给用户
alter user scott temporary tablespace GROUP2;
5）在数据库级设置临时表空间
alter database <db_name> default temporary tablespace GROUP2;
6）删除临时表空间组 (删除组成临时表空间组的所有临时表空间)
drop tablespace tempts1 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP2 TEMPTS2
drop tablespace tempts2 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
三、临时表空间 shrink 操作
对临时表空间进行shrink（11g新增功能）
排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是 ORACLE11g 新增的功能。
Large sort operations performed by the database mayresult in a temporary tablespace growing and occupying a considerable amount ofdisk space. After the sort operation completes, the extra space is notreleased; it is just marked as free and available for reuse. Therefore, asingle large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. Forthis reason, the database enables you to shrink locally managed temporary tablespaces and release unused space.
数据库执行的大型排序操作可能会导致临时表空间的增长和占用大量的磁盘空间。排序操作完成后，额外的空间不会被释放；它只是被标记为免费的，可以重用。因此，单个大型排序操作可能会导致大量分配的临时空间，这些空间在排序操作完成后仍未使用。由于这个原因，数据库使您能够缩小本地管理的临时表空间并释放未使用的空间。
You use the SHRINK SPACE clause ofthe ALTER TABLESPACE statement to shrink a temporary tablespace, orthe SHRINK TEMPFILE clause of the ALTER TABLESPACE statement toshrink a specific temp file of a temporary tablespace. Shrinking frees as muchspace as possible while maintaining the other attributes of the tablespace ortemp file. The optional KEEP clause defines a minimum size for the tablespace or temp file。
可以使用 ALTER TABLESPACE 语句的 SHRINK SPACE 子句来收缩临时表空间，或者使用ALTER TABLESPACE statement 的 SHRINK TEMPFILE 子句来收缩临时表空间的特定临时文件。在保持表空间或临时文件的其他属性的同时尽可能减少空间。可选的 KEEP 子句为表空间或临时文件定义了最小大小。
Shrinking is an online operation, which means that usersessions can continue to allocate sort extents if needed, and already-runningqueries are not affected.
收缩是一个在线操作，这意味着用户会话可以继续在需要时分配排序区，并且已经运行的查询不受影响。
--将 temp 表空间收缩为10G
alter tablespace temp shrink space keep 10G;
--自动将表空间的临时文件缩小到最小可能的大小
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u01/app/oracle/oradata/temp01.dbf’;
--查看临时表空间大小
Select * from dba_temp_free_space;
Select * from v$temp_space_header;
select tablespace_name,file_id,bytes/1024/1024 MB,blocks,maxbytes/1024/1024 MB,maxblocks from dba_temp_files;
--这个语句还是很好用的，快收藏哦！
selectd.tablespace_name "Name",d.status"Status",TO_CHAR(NVL(a.BYTES/1024/1024,0),'99,999,990.90') "Size(M)",
TO_CHAR(NVL(t.BYTES,0)/1024/1024,'99999999.99')USE,TO_CHAR(NVL(t.BYTES/a.BYTES*100,0),'990.00') "Used%"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name,SUM(BYTES) BYTES FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name,SUM(bytes_cached) BYTES FROM v$temp_extent_pool GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY';
前面指定大小为 16G，报错ORA-03297：文件包含在请求的 RESIZE 值以外使用的数据。偏小了，若为 18G则不会报错，如果不指定保留空间大小， shrink space 会收缩临时表空间到最小的可能空间大小。
这样就解决了临时表空间使用率过大的问题，当然，也可以使用 resize 临时文件来解决，那么需要 resize 到多少才算合适呢？我们可以查询临时空间使用情况来判断，视图v$sort_usage 记录了排序空间的使用情况，找到最大的块号。
Select (Selectmax(segblk#) from v$sort_usage)*8192/1024/1024/1024 Gb from dual;
Alter databasetempfile '/oracle/oradata/ORCL/datafile/temp01.tmp' resize 18G;
总结：Shrink 操作完后，部分行数据的 rowid 发生了变化，同时，部分行数据的物理存储的顺序也会发生变化，而 table 所位于的 block 的区域却没有变化。所以，shrink 只移动了 table 其中一部分的行数据，来完成释放空间，而且，这个过程是在 table 当前所使用的 block 中完成的。
参考链接：
https://www.cnblogs.com/einyboy/archive/2012/08/10/2631464.html
https://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN01103