Oracle数据库临时表管理技巧

发布时间:2017-01-23 00:00:00 编辑:嘉辉 手机版

  我们在创建数据表的时候,若没有特殊的指明,那么我们创建的表是一个永久的关系型表格,也就是说,这个表格中对应的数据,除非是我们显示的删除的话,表中的数据是永远都存在的。相对应的,在Oracle数据库中还有一种类型的表,叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远的存在。当一个会话结束或者事务结束的时候,这个临时表中的数据,不用用户自己删除,数据库自己会自动清除。

  1、 事务临时表的管理。

  (1) 事务临时表的创建。

  Oracle数据库根据临时表的性质不同,可以分为事务临时表与会话临时表。事务临时表是指数据只有在当前事务内有效。一般情况下,如果在创建数据表的时候,没有特殊指明这表是会话临时表的话,则该表默认为事务临时表。

  我们可以以下面的语句创建事务临时表。


  Create global temporary table Temp_user 
  (ID NUMBER(12) Primary key,name varchar2(10));

  笔者建议:

  这个创建临时表的语句中,虽然没有显性的指明该表是事务临时表,但是,默认的情况下,若没有指明是什么临时表的话,系统默认是事务临时表。我们要创建事务临时表时,可以不指定关键字。但是,这查看起来比较麻烦。我建议,无论在建立什么临时表,都要利用具体的关键字来显形的指明,这大家看起来都方便。一般可以利用ON COMMIT DELETE ROWS关键字来说明该表就是事务性的临时表,而不是会话性质的临时表。

  (2) 事务临时表数据的变化分析。

  事务临时表的话,当事务结束的时候,就会清空这个事务临时表。所以,当我们在数据库临时表中插入数据后,只要事务没有提交的话,该表中的数据就会存在。但是,当事务提交以后,该表中的数据就会被删除。而且,这个变化不会在重做日志中显示。

  具体事务临时表与会话临时表有什么区别,我们在介绍完会话临时表后会详细介绍。

  2、 会话临时表的管理。

  会话临时表,顾名思义,是指数据只在当前会话内有效的临时表。关闭当前会话或者进行新的连接之后,数据表中的内容就会被清除。那会话临时表跟事务临时表到底有什么区别呢?我们以一个实例来看其中的区别。

  (1) 首先,创建一个会话临时表。


  CREATE GLOBAL TEMPOPARY TABLE TEMP_USER 
  (ID NUMBER(12) Primary key,name varchar2(10)) 
  ON COMMIT PRESERVE ROWS;

  也就是说,会话临时表跟事务临时表的创建语法大致相同,只有最后的关键字有区别。不过两个表虽然类似,但是其内部的处理机制还是有比较大的区别。

  (2) 往该表中插入数据。


 Insert into TEMP_USER values(1001,’victor’);

  往数据库临时表中插入数据的方法,跟往普通表中插入数据的方法是一样的,都利用insert into语句进行操作。该临时表的数据在会话结束之前都是存在这个表格中的。

  (3) 提交该事务并查询相关记录。

  我们利用COMMIT的语句把该事务提交以后,再用SELECT查询语句进行查询。我们知道,若该表是事务临时表的话,则当该事务结束以后,该表中的内容就会被删除。但是,这是会话临时表,所以即使该事务提交了,但是,利用SELECT语句进行查询时,仍然可以查到该条员工记录。

  (4) 结束当前会话,并重新连接数据库。

  关闭当前会话,从新连接到数据库后,再利用SELECT语句查询时,会有什么结果呢?此时,就查不到我们刚才插入的数据。这也就是说,在关闭对话的时候,数据库系统已经把原有的数据删除了。从以上的分析我们可以看中,会话临时表与事务临时表主要的差异就在于删除数据时机的不同。事务性临时表是在事务提交的时候清除数据,而会话性临时表则是在关闭当前会话的时候清除临时表。只要当前会话没有关闭,即使事务完成了,会话临时表中的数据仍然存在,不会被清除。

  3、 临时表管理需要注意的地方。

  临时表相对与其他表来说,是一种比较特殊的表结构,但是,作用又比较大,Oracle数据库若没有这种表的话,还真是不行。为了管理好这种特殊的表,我们需要注意几个细节。

  一是要注意临时表不能永久的保存数据。只所以称为临时表,就是因为该表中的内容只是临时存在的。当一个会话或者事务结束时,该表中的内容就会被自动清空。所以,在临时表中,一般不要保存永久数据。在实务中,有个不好的操作习惯,就是有些人在测试数据库的时候,喜欢把测试的数据放在临时数据表中。其实,这是对Oralce临时数据表认识的错误。若我们在数据库中,把要测试的数据,如销售定单的内容放在数据库的临时表中的话,则在其他功能中,如要测试销售定单日报表的功能时,就会找不到相关的定单内容。因为离开特定的会话或者事务的话,临时表中的内容就会不存在了。所以,Oralce数据库中所讲的临时表不是给我们来存储测试数据的。

  二是临时表中的数据不会备份、恢复,对其的修改也不会有任何的日志信息。若我们在操作数据库的时候,往数据库的临时表中存入了一些信息。此时突然服务器出现当机。此时,我们想通过数据库备份文件恢复数据库临时表中的内容,或者查看临时表的日志信息,都是无法实现的。也就是说,当服务器以外死机重新启动后,临时表中的内容就会被清空。在数据库的任何地方,如数据库备份文件或者日志信息中,都查不到在重新启动之前数据库临时表中保存了哪些内容,就好象根本没有对临时表进行操作一样。

  三是临时表表空间的管理。临时表在Oracle数据库中,也是表的一种,其也有对应的表空间。在创建临时表的时候,若我们不指定表空间的话,默认的表空间是SYSTEM。对于临时表的表空间管理的话,我们需要注意一个小的细节。若我们把临时表的表空间归属为SYSTEM的话,也就是说,在创建临时表的时候不具体指定具体的表空间,则这个默认的表空间是不能被删除的。而若我们在创建临时表表空间的时候,指定为SYSTEM以外的表空间的话,则在不需要这表空间的时候,我们可以删除。所以,为了后续管理的方便,笔者还是建议大家在创建临时表的时候,要指定表空间。

  四是要注意一个问题,临时表只是数据是临时的,而表仍然是永久的。也就是说,当一个会话结束或者一个事务完成时,其临时表中的数据虽然删除了,但是,临时表本身仍然是存在的。也就是说。Oracle数据库中的临时表表是全局的,只是数据是临时的。这跟SQL Server数据库系统具有比较大的区别。其实,这两个数据库在临时表的处理上有很大的不同,各有各的特色。在以后的文章中,我会专门叙述这两种数据库在临时表管理机制上的不同,欢迎大家关注。

  五是要注意Oracle数据库在给临时表填入数据的时候,不会对相应的记录加锁。也就是说,当在临时表上执行DML语句的操作时,不会给记录加锁,也不会将数据的变化内容写到重做(REDO)日志中。所以不能用临时表保存永久的数据,也不能对临时表进行共同的操作。这是新手在管理数据库临时表经常会碰到的问题。

  六是临时表与普通表之间不能相互转换。在一般情况下,临时表建立后,该表就不能被转换成永久表。所以,这也说明一个道理,利用临时表作为数据库设计时候的测试表不合适。这个临时表可能跟我们按字面意思理解的临时表有误,不是我们所认为的为了测试表结构而建立的临时表。这一点是我们在刚开始接触ORACLE数据库时,经常会犯的错误。

本文已影响870
+1
0