Oracle Temp临时表空间及其故障处理
Oracle 11g中Temp临时表空间、文件的新特性
临时表空间是Oracle体系结构中比较特殊的结构。通常情境下，数据库使用者只需要设置对应的临时表空间（到用户），临时段分配等工作都是系统自动完成。当临时数据不需要时，Oracle后台进程SMON也会负责将临时段回收。
在Oracle的备份恢复体系中，临时文件的地位比较低。在进行备份动作时，RMAN都不会进行临时文件恢复。在恢复启动过程中，如果发现临时文件不存在，通常Oracle也会自动将临时文件创建出来。
1、Temp漫谈
Oracle临时表空间主要充当两个主要作用：临时表数据段分配和排序汇总溢出段。我们创建的临时表，在使用过程中，会有大量的数据段结构的分配。这个分配就是利用临时表空间。
排序汇总溢出的范围比较广泛。我们在SQL语句中进行order by/group by等操作，首先是选择PGA的内存sort area、hash area和bitmap area。如果SQL使用排序空间很高，单个server process对应的PGA不足以支撑排序要求的时候，临时表空间会充当排序段的数据写入。这样排序动作会从内存过程退化为外存储过程。
两个现象：如果我们的Temp表空间文件设置比较小，并且设置为不可自动拓展。同时我们又希望给一个很大数据表加索引，经常会遇到：create index语句长时间运行之后报错，说Temp表空间不能拓展，操作被停止。索引叶子节点是有序的，创建索引的过程也就伴随着数据库的排序动作。
另一个现象：如果我们的内存设置不合理，SQL经常包括很多“无意义”的“大排序”。这样会发现我们的Temp空间消耗比较大，一些SQL性能抖动比较明显。
合理的设置Temp空间管理策略，是应用系统架构的一个重要环节。
2、给临时表指定表空间
Oracle中，用户schema和表空间存储结构对应关系是很灵活的。如果用户有空间配额（Quota），我们是可以在schema中创建任何表空间的数据表的，是可以把对象放置在任何的表空间里面。
但是对于11g之前，Temp表空间并不是这样的。我们创建用户之后，需要制定出这个用户schema对应的临时表空间。如果我们不指定，Oracle会选择系统默认临时表空间（通常是temp）作为这个用户的临时表空间。
至此以后，这个用户所有的临时段都是在这个临时表空间上进行分配。我们是没有能力指定某个临时表分配在其他临时表空间里面的。
我们到11g之后，Oracle提供了这样的自由。
SQL> select * from v$version;
BANNER
-----------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 – Production
当前sys用户的默认表空间为TEMP。
SQL> select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SYS';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP
此时，数据库中包括两个临时表空间。
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
TEMP 481296384 481296384 480247808
TEMPTEST 5368709120 1048576 5367660544
我们可以创建出一个不属于TEMP默认临时表空间的临时表。
SQL> create global temporary table t_temp tablespace temptest as select * from t where 1=0;
Table created
此后的临时段分配，都是在temptest表空间上进行的。
SQL> insert into t_temp select * from t;
19360512 rows inserted
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
TEMP 481296384 481296384 480247808
TEMPTEST 5368709120 2248146944 3120562176
那么，是不是和数据表一样，支持move操作呢？
SQL> create global temporary table t_temp tablespace temp as select * from dba_objects where 1=0;
create global temporary table t_temp tablespace temp as select * from dba_objects where 1=0
ORA-14451: unsupported feature with temporary table
看来，目前版本还没有支持move操作的临时表。
3、临时表空间、文件的shrink
临时表空间是不会有持久化数据保存的。所以，很多被“胀大”的表空间都存在一个shrink收缩问题。从11g开始，Oracle支持Temp表空间和临时文件的搜索方法。
为了进行试验，我们先向使用表空间Temptest添加文件。
SQL> alter tablespace temptest add tempfile size 1G;
Tablespace altered
SQL> select file_name, file_id, tablespace_name from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------ ---------- ---------------
/u01/app/oradata/ORA11G/datafi 1 TEMP
le/o1_mf_temp_92t73qm8_.tmp
/u01/app/oradata/ORA11G/datafi 2 TEMPTEST
le/o1_mf_temptest_9j80859z_.tm
p
/u01/app/oradata/ORA11G/datafi 3 TEMPTEST
le/o1_mf_temptest_9j826c9b_.tm
p
空间情况：
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
TEMP 481296384 481296384 480247808
TEMPTEST 6442450944 2249195520 6440353792
新加入临时文件到临时表空间，由于文件采用稀疏文件结构，所以我们allocated_space没有增加，而free_space有增加。
磁盘空间也不会变化。
[root@SimpleLinux ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 48G 26G 20G 57% /
tmpfs 6.0G 256M 5.8G 5% /dev/shm
/dev/mapper/VolGrp01-lv1
194M 5.6M 179M 4% /voltest01
[root@SimpleLinux ORA11G]# cd datafile/
[root@SimpleLinux datafile]# ls -l | grep temptest
-rw-r----- 1 oracle oinstall 5368717312 Feb 19 09:10 o1_mf_temptest_9j80859z_.tmp
-rw-r----- 1 oracle oinstall 1073750016 Feb 19 09:28 o1_mf_temptest_9j826c9b_.tmp
我们可以直接使用shrink tempfile的方法，将文件限制大小。Keep字句中包括控制大小。
SQL> alter tablespace temptest shrink tempfile '/u01/app/oradata/ORA11G/datafile/o1_mf_temptest_9j826c9b_.tmp' keep 500m;
文件系统中，文件显示出的大小便为500M，但是磁盘分配没有变化，因为从来就没有分配过。
[root@SimpleLinux datafile]# ls -l | grep temptest
-rw-r----- 1 oracle oinstall 5368717312 Feb 19 09:10 o1_mf_temptest_9j80859z_.tmp
-rw-r----- 1 oracle oinstall 525336576 Feb 19 09:35 o1_mf_temptest_9j826c9b_.tmp
[root@SimpleLinux datafile]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 48G 26G 20G 57% /
tmpfs 6.0G 256M 5.8G 5% /dev/shm
/dev/mapper/VolGrp01-lv1
194M 5.6M 179M 4% /voltest01
缩小的500M，在dba_temp_free_space中有所表现。
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
TEMP 481296384 481296384 480247808
TEMPTEST 5894037504 2249187328 5891948544
如果我们对那个已经分配的临时文件进行shrink，是会影响到磁盘结构的。
SQL> alter tablespace temptest shrink tempfile '/u01/app/oradata/ORA11G/datafile/o1_mf_temptest_9j80859z_.tmp' keep 1G;
Tablespace altered (长时间执行)
[root@SimpleLinux datafile]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 48G 25G 21G 55% /
tmpfs 6.0G 256M 5.8G 5% /dev/shm
/dev/mapper/VolGrp01-lv1
194M 5.6M 179M 4% /voltest01
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
TEMP 481296384 481296384 480247808
TEMPTEST 1600110592 2080768 1598029824
此外，我们也是可以对Temp表空间直接进行shrink过程。
SQL> create temporary tablespace temptest tempfile size 1G
2 extent management local uniform size 1m;
Tablespace created
SQL> alter tablespace temptest shrink space keep 500m;
Tablespace altered
此时检查视图dba_temp_free_space。
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 30408704 30408704 29360128
TEMPTEST 525336576 1048576 524288000
影响到的就是表空间总大小。如果我们不指定keep，Oracle会将表空间缩小到元数据阶段。
SQL> alter tablespace temptest shrink space;
Tablespace altered
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 30408704 30408704 29360128
TEMPTEST 2088960 1040384 1048576
确定了2M大小，1M是分配元数据。
4、结论
11g中提供了很多临时表空间操作的特性，这帮助我们更好的管理和控制临时表空间。
一次临时表空间大量占用问题的处理
一个电信运营商客户的核心交易系统，临时表空间大量被占用，临时表空间被撑到了600GB。这样的问题复杂吗？取决于很多因素，不过今天所要讲的案例，并不复杂，如果我们对临时表空间在何种情况下使用有足够了解。
首先，我们要去检查是什么会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么。正如我们要想知道这个月的花费过大，去分析原因时就要去看是哪些开销过大、开销了多少金额、开销的用途等。
这个步骤比较简单，查询v$sort_usage就可以了：
- <
- >
- >
- > >
- <
- <
- <
- <
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
