临时表相关 (r4笔记第52天)

临时表在日常工作中可能使用比较多，但是大家都对临时表相关的一些知识了解比较少。我们来简单说数理一下。 首先是临时表空间，临时表都存储在临时表空间中，对于临时表空间，从数据库中查询数据字典就能够很清楚的看到，临时表空间是nologging的，也就是临时表也是nologging的。

SQL> select tablespace_name,logging from dba_tablespaces;

TABLESPACE_NAME LOGGING ------------------------------ --------- SYSTEM LOGGING SYSAUX LOGGING UNDOTBS LOGGING TEMPTS1 NOLOGGING TEST_DATA1 LOGGING POOL_DATA LOGGING TEST_DELETE LOGGING TEST LOGGING

8 rows selected. 其次是临时文件，临时表空间的物理存储就是临时文件，从物理层面来说，还是有一些玄机。临时文件感觉和11g的一个特性deferred_segment_creation有些像。 首先如果你查看数据文件相关的数据字典是找不到临时文件的身影的。 select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- SYSTEM /u03/ora11g/oradata/TEST01/system01.dbf SYSAUX /u03/ora11g/oradata/TEST01/sysaux01.dbf UNDOTBS /u03/ora11g/oradata/TEST01/undotbs01.dbf TEST_DATA1 /u02/ora11g/testdata1.dbf POOL_DATA /u03/ora11g/oradata/TEST01/pool_data03.dbf POOL_DATA /u03/ora11g/oradata/TEST01/pool_data01.dbf POOL_DATA /u03/ora11g/oradata/TEST01/pool_data02.dbf POOL_DATA /u03/ora11g/oradata/TEST01/pool_data04.dbf POOL_DATA /u03/ora11g/oradata/TEST01/pool_data05.dbf POOL_DATA /u01/ora11g/pool_data06.dbf POOL_DATA /u01/ora11g/pool_data07.dbf TEST_DELETE /u01/ora11g/test_delete.dbf TEST /u01/ora11g/test01.dbf

13 rows selected. 只能够从专有的数据字典dba_temp_files中查到。之前做过一些使用rman的备份恢复，哪怕是全库恢复，最后临时文件也不包含，这个临时文件还是需要单独创建。因为它临时的角色。

SQL> select file_name,tablespace_name ,bytes,status from dba_temp_files;

FILE_NAME TABLESPACE_NAME BYTES STATUS -------------------------------------------------- ------------------------------ ---------- ------- /u03/ora11g/oradata/TEST01/temp01.dbf TEMPTS1 419430400 ONLINE 我们来简单演示一下临时文件的玄机，首先在创建一个新的临时表空间的时候，我们查看磁盘空间情况。发现还剩余13M左右。 [ora11g@rac1 TEST01]$ df -k|grep u03 /dev/sdb3 7906196 7490784 13788 100% /u03 这个时候我们创建一个临时表空间，定义大小为10M,这个时候剩余空间应该为2~3M左右。

SQL> create temporary tablespace tempts2 tempfile '/u03/ora11g/oradata/TEST01/temp02.dbf' size 10M;

Tablespace created. 但是实际上空间还剩余很多，只占用了很小的一部分空间。

SQL> !df -k|grep u03 /dev/sdb3 7906196 7491820 12752 100% /u03

但是奇怪的是查看临时文件的大小，却是实实在在的10M. [ora11g@rac1 TEST01]$ ll temp02.dbf -rw-r----- 1 ora11g dba 10493952 Feb 20 06:35 temp02.dbf 这种文件叫做稀疏文件，也就是说在需要使用之前不会占用真正的存储空间。 说完了临时表空间，临时文件，可以扩展说一下临时表空间组。 临时表空间的好处也比较明显，主要就是把多个临时表空间放在一起用，这样就可以完成一些略微复杂的功能。主要功能是： 防止一个临时表空间出现空间不足的问题。 同一个用户同时连接多个session时可以使用不同的临时表空间。 在并行操作中可以并行使用多个临时表空间。 至于创建的过程也是很简单的，比如我们新创建一个临时表空间tempts2，然后把已有的临时表空间tempts1和信创建的tempts2都放到临时表空间组temp中。

SQL> create temporary tablespace tempts2 tempfile '/u03/ora11g/oradata/TEST01/temp02.dbf' size 10M;

Tablespace created. 这个时候查看，没有发现表空间组的存在。 SQL> select * from dba_tablespace_groups;

no rows selected

我们可以简单的使用alter tablespace语句把临时表空间放入对应的组中。 SQL> alter tablespace tempts2 tablespace group temp;

Tablespace altered.

SQL> alter tablespace tempts1 tablespace group temp;

Tablespace altered.

查看临时表空间组的情况 SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEMP TEMPTS1 TEMP TEMPTS2 如果要取消也很容易。

SQL> alter tablespace tempts2 tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEMP TEMPTS1 我们再来说说最后的要点临时表， 临时表主要用于存放食物或者会话中的临时结果集。比如排序和hash操作的结果，临时表中的数据只对当前会话可见，在这一点上临时表又分为两种，在具体使用的时候可以灵活的使用。 我们创建两个临时表，不同之处就在于标黄的部分。

SQL> create global temporary table temp_session on commit preserve rows as select *from user_tables where rownum<5;

Table created.

SQL> create global temporary table temp_transaction on commit delete rows as select *from user_tables where rownum<5;

Table created.

temp_session是基于会话级别的，在同一个会话中，提交之后数据还是存在，除非指定了delete或者truncate操作，而temp_transaction是基于事务层面的，在commit的时候就会自动清空数据。 我们来简单的看一下，在同一个session中。查看temp_session的数据情况。在temp_session中因为还是当前session，所以数据还是存在可见，但是temp_transaction中数据就不可见。因为ddl相当于auto commit的事物已经完成了。

SQL> select count(*)from temp_session;

COUNT(*) ---------- 4

SQL> select count(*)from temp_transaction;

COUNT(*) ---------- 0

我们尝试删除temp_session中的数据。

SQL> delete from temp_session;

4 rows deleted.

SQL> select count(*)from temp_session;

COUNT(*) ---------- 0 然后做回退操作。

SQL> rollback;

Rollback complete. 发现数据还是能够回退回来。

SQL> select count(*)from temp_session;

COUNT(*) ---------- 4

断开连接 SQL> disc Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn n1/n1 Connected. 数据就都没了。 SQL> select count(*)from temp_session;

COUNT(*) ---------- 0

SQL> select count(*)from temp_transaction;

COUNT(*) ---------- 0

临时表的使用还是比较广泛的，而且临时表中的数据都是对当前session可见，有session级和事务级的生命周期，不存在并发的困扰。合理的使用还是能够发挥很好的效果。