临时表相关 (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级和事务级的生命周期,不存在并发的困扰。合理的使用还是能够发挥很好的效果。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-02-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏猿人谷

【性能提升神器】STRAIGHT_JOIN

今天给大家下另一个性能提升神器-STRAIGHT_JOIN,在数据量大的联表查询中灵活运用的话,能大大缩短查询时间。

35020
来自专栏Linyb极客之路

MySql查询性能优化

在访问数据库时,应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源,并增加网络开销。 例如在处理分页时,应该使用LIMIT限制My...

20840
来自专栏Grace development

MySQL SQL模式特点汇总

MySQL服务器可以在不同的SQL模式下运行,并且可以针对不同的客户端以不同的方式应用这些模式,具体取决于sql_mode系统变量的值。DBA可以设置全局SQL...

16520
来自专栏Jackson0714

【SQL进阶】03.执行计划之旅1 - 初探

35570
来自专栏腾讯云数据库团队的专栏

phpMyAdmin 中 sql-parser 组件的使用

phpMyAdmin 是一款基于 Web 端运行的开源数据库管理工具,它的安装使用都比较简单而且已有很多相关介绍不再重复,今天要介绍的是源码中的一个核心组件 s...

2.6K10
来自专栏Jackson0714

【SQL进阶】03.执行计划之旅1 - 初探

13010
来自专栏乐沙弥的世界

Oracle 绑定变量窥探

    Bind Peeking是Oracle 9i中引入的新特性,一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候,查看一下当前...

33330
来自专栏后端技术探索

MySQL中的两种临时表 外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这...

9100
来自专栏沃趣科技

用于修改配置的存储过程 | 全方位认识 sys 系统库

在本系列中前面用了大量篇幅介绍完了 sys 系统库的视图,利用这些视图我们可以方便快捷地查询到performance_schema、information_sc...

10930
来自专栏MYSQL轻松学

MySQL replace用法简介

今天在工作的过程中碰到一个问题,要把数据库中某个列的所有值中含有"ceshi.test.com"的字符去掉,本来可以写个脚本,把所有的值都取出再导入进行处理,但...

39490

扫码关注云+社区

领取腾讯云代金券