Oracle临时表总结

Oracle临时表总结（精选4篇）

篇1：Oracle临时表总结

Oracle数据库临时表管理心得

我们在创建数据表的时候，若没有特殊的指明，那么我们创建的表是一个永久的关系型表格，也就是说，这个表格中对应的数据，除非是我们显示的删除的话，表中的数据是永远都存在的。相对应的，在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数据库时，经常会犯的错误。

篇2：Oracle临时表总结

Oracle中的临时表是全局的，需要在数据库设计时创建完成，而不是程序使用时，每个登陆用户都使用这一个相同的临时表，但互相之间看不到彼此的数据，也就是说临时表是会话独立的。

Oracle 的临时表分为事务型和会话型。

事务型临时表就是指在事务处理过程中插入的记录只在事务提交或回滚之前有效，一旦事务完成，表中的记录便被自动清除。

会话型临时表指临时表中的数据在本次会话期间一直有效，直到会话结束。会话结束后表中针对此次会话的数据会自动清空。

Oracle 临时表的不足之处：

1、不支持lob对象，例如 Spatial 的SDO_GEOMETRY.这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。

2、不支持主外键关系。这意味着临时表

鉴于以上原因，洒家设计了一份自定义的临时表处理办法，使之可以支持Oracle Spatial数据类型和主外键关系，而且不会出现并发冲突，

1、以常规表的形式创建临时数据表的表结构，但要在每一个表的主键中加入一个 SessionID 列以区分不同的会话。(可以有lob列和主外键)

2、写一个用户注销触发器，在用户结束会话的时候删除本次会话所插入的所有记录(SessionID等于本次会话ID的记录)。

3、程序写入数据时，要顺便将当前的会话ID(SessionID)写入表中。

4、程序读取数据时，只读取与当前会话ID相同的记录即可。

功能增强的扩展设计：

1、可以在数据表上建立一个视图，视图对记录的筛选条件就是当前会话的SessionID.

2、数据表中的SessionID列可以通过Trigger实现，以实现对应用层的透明性。

3、高级用户可以访问全局数据，以实现更加复杂的功能。

扩展临时表的优点：

1、实现了与Oracle的基于会话的临时表相同的功能。

2、支持SDO_GEOMETRY等lob数据类型。

3、支持表间的主外键连接，且主外键连接也是基于会话的。

篇3：Oracle临时表总结

关键词：表性能,高效移除,跨数据块

一引言

Oracle数据库系统是美国ORACLE公司 (甲骨文) 提供的以分布式数据库为核心的一组软件产品, 是目前最流行的C/S或B/S体系结构的数据库之一。Oracle数据库是目前世界上使用最为广泛的数据库管理系统, 作为一个通用的数据库系统, 它具有完整的数据管理功能。组成Oracle数据库的物理结构用来存储、管理、保护以及读取数据, 在创建数据库时, 对数据文件初始布局以及表空间的管理类型对性能都有较大的影响。实际项目中使用的Oracle数据库经过一段时间的运行, 在线保存的数据量和业务处理的数据量在逐渐增大, 最初的Oracle设置, 与现在实际需要的运行性能有一定差距, 需要进行一些优化调整。

二创建最优性能的数据库

合理地配置并创建数据库有助于保证数据库的优良性能, 并且数据库大小会随着使用时间成倍增长, 因此创建最优性能的数据库一般具有以下几个原则:

1.强制数据库中创建的每一个表空间都必须是本地管理的。本地管理表空间能比已经被弃用的字典管理技术提供更好的性能。

2.确保数据库为每个用户自动分配一个默认的永久表空间。这可以保证在创建用户时, 自动分配一个默认的表空间, 而不是SYSTEM系统表空间。你不能让用户总是将对象建在SYSTEM表空间中, 因为这样会对性能和可用性产生负面影响。

3.确保数据库会为每个用户自动分配一个默认的临时表空间。这可以保证在创建用户时, 自动分配一个临时表空间, 而不是SYSTEM表空间。你不能总是让用户使用SYSTEM表空间作为排序操作运算的临时表空间, 因为这样会对性能和可用性产生负面影响。

如创建以下数据库:

SQL>Create database oralgz

Maxlogfiles 16

Maxlogmembers 4

Maxinstanecs 1

Maxloghistory 500

Datafile‘/ora01/dbfile/oralgz01/system01.dbf’ size 300M

Undo tablespace undotbs

Datafile‘/ora01/dbfile/oralgz01/undo01.dbf’ size 100M

Default temporary tablespace temp

Tempfile‘/ora01/dbfile/oralgz01/temp01.dbf’ size 60M

创建数据库时, 还需要考虑影响可维护性的功能。容易维护的数据库能正常运行更长时间, 而这也正是总体性能的一个重要部分。“解决方案”部分的CREATE DATABASE语句同时还考虑了下面这些可维护性方面的特性。 (1) 创建一个自动的UNDO表空间。这使Oracle可以自动管理回滚段, 你也就不必定期进行监控和微调了。 (2) 按照环境中的一定标准, 将数据文件放到相应文件夹中。这有助于维护和管理, 从而使数据库具备更好的长期可用性, 从而获得更好的性能。

三高效移除表中数据

在对数据库进行操作时, 如果需要在移除数据以后还能选择进行数据回滚 (而不是立即提交) , 那么就应该使用Delete语句。但是, Delete语句的缺点是它会产生大量的撤销 (undo) 和重做信息。因此, 对于大表来说, Truncate语句通常是移除数据最有效的方法。

Truncate语句的另一个特性就是它会将表的高水位线重新归零。当你使用Delete语句移除表中数据时, 高水位线将不会发生变化。使用Truncate语句并重置高水位线的一个优点就是, 全表扫描查询仅搜索位于高水位线之下的存储块中的数据行。这对于进行全表扫描的查询性能具有很大的影响。

如:使用Truncate语句移除COMPUTER_SYSTEMS表中的所有数据

SQL>Truncate table comput_systems;

Truncate语句的另一个副作用就是, 如果一张表定义了主键, 并且这个主键是其子表的外键, 那么不能截断该表, 即使这个子表包含零个数据行也是如此。在这种场景下, 试图截断父表时, Oracle将会抛出下面这个错误:

Ora-02266:unique/primary keys in table referenced by enabled foreign keys

Oracle之所以会阻止你截断父表, 是因为在一个多用户系统中, 有可能在截断子表与接下来截断父表之间的这段时间里, 另一个会话向子表中填充数据行。在这种情况下, 必须暂时禁用子表所引用的外键约束, 执行Truncate语句, 然后再重新启用约束。

比较Truncate语句和Delete语句的功能。Oracle的确允许使用Delete语句从父表中移除数据行, 而不管是否有指向子表的约束存在 (假设子表中的数据为零行) 。这是因为Delete会生成重做, 具有读一致性, 并且能够回滚。表1-1总结了Delete和TruncateE之间的区别。

如果需要使用Delete语句, 就必须使用COMMIT或ROLLBACK来结束事务。提交一条Delete语句就使得数据永久消失:

SQL> Delete from computer_systems;

SQL>commit;

如果提交一条ROLLBACK语句而不是COMMIT, 那么表中的数据将会与执行Delete语句之前一样。

使用DML语句时, 可以查询V$TRANSACTION视图来确认事务的细节。例如, 假设你往表中插入了数据, 在进行COMMIT或ROLLBACK之前, 能够看到当前所连接会话的活动事务信息如下所示:

SQL>insert into computer_systems (cs_id) values (1) ;

SQL>select xidusn, xidsqn from v$transaction;

Xidusn xidsqn

3 12878

SQL> commit;

SQL>select xidusn, xidsqn from v$transaction;

No rows selected

四重建跨多个数据块的数据行

当有一张表, 其中的数据行可能会存储在多个数据块中。这种情况会增加I/O使用率, 并导致对这张表的查询运行速度变慢。因此需要重建跨多个数据块的数据行, 使每一行数据位于一个数据块中, 从而提高表的性能。但是从表中取出数据时, 可能会受到行链接的影响, 因此必须删除表中的行链接。

解决行链接问题的一个办法就是使用MOVE语句。移动一张表时, Oracle需要表上的排它锁。因此当要进行移动操作的表没有活动事务时, 应该执行MOVE操作。同时, 作为移动操作的一部分, 所有数据行都会被分配一个新的行编号 (ROWID) 。这会使表的所有索引失效。因此作为移动操作的一部分, 你需要重建所有与进行移动的表相关的索引。如移动EMP表:

SQL>alter table emp move;

在数据块中保留有一定量的空间, 以便容纳数据行的增长。通常数据行大小增加是由于执行了增加列长度值的UPDATE语句。如果块中没有足够的空间来容纳所增长的数据, 那么Oracle就会创建一个指针, 指向具有足够空间的数据块, 并在该数据块中存储一部分行数据。当一个数据行存储在两个或多个数据块中时, 就称为行链接。这可能会导致潜在的性能问题, 因为Oracle将不得不从多个数据块 (而不是一个) 中取链接的一行数据。

少量的行链接不会对性能有很大的影响。一个大致的准则是, 如果表中超过15%的数据行是链接的, 那么你就要执行正确的操作了 (例如移动表以重新进行组织) 。

数据块中所保留的空闲存储空间大小由表的存储参数PCTFREE决定。PCTFREE的默认值为10, 也就是数据块保留10%的空间, 以备更新操作使用。如果某张表中的列初始插入空值 (null) , 更新之后包含较大的值, 那么就要考虑将PCTFREE设置为更高的值, 例如40%。这将有助于阻止行链接的产生。

相反, 如果你有一张表, 在插入数据行之后就再也不会更新, 那么可以考虑将PCTFREE值设置为0。这可以使每个数据块中容纳更多的行, 从而减少取出数据时需要读取的磁盘空间数量 (也就提高了性能) 。

参考文献

[1]陈雍.基于ORACLE数据库应用系统性能调整和优化研究[学位论文], 江西师范大学, 2008

[2]Vaidyanatha.G.K等.Oracle性能优化技术内幕.北京:机械工业出版社, 2002.5

篇4：Oracle临时表总结

临时表在数据库设计中，有着举足轻重的作用，

如我们可以利用临时表的功能来限制同一个用户名多次登陆到同一个系统中去。例如现在有一个财务管理系统，企业希望同一个用户在同一时间里只能登陆一次，这主要是用来限制每位员工都以自己的用户名与密码登陆。如此限制的目的主要是为财务管理系统中的每张单据找到其主人。

原始方案：

在以前数据库设计的时候，也有人不用临时表进行这方面的限制，而用实体数据库表来登记相关的信息。如在用户信息表中有一列专门用来记录用户的当前登陆状态。当用户登陆系统后，该用户登陆状态的字段就改为Y，而当用户退出系统能后，该字段的内容又改为N。这个方案看起来是可行的，但是，其在实际应用中，有一个非常大的漏洞。若用户登陆到系统后，终端因为各种原因，如病毒、断电等突然状况，发生死机的话，此时，用户虽然没有登陆到系统中去，但是，因为其退出系统的时候，没有正常退出，这就导致在财务管理系统中的用户信息表中，显示该用户的登陆状态仍然为Y。此时，用户尝试登陆到财务管理系统中去的话，就会被系统拒绝，系统会认为该用户已经登陆了系统，不能重复登陆。

所以说，利用实体表来记录用户登陆的信息，存在着管理上的漏洞。

利用临时表实现用户重新登陆的限制：

后来，数据库设计师们想，能否把该用户登陆信息记录在一张临时性的表中呢?当用户结束会话，无论是正常的退出还是因为意外情况的退出，只要用户结束一个会话后，那么该临时表中的内容就会清空。

若跟这个需求结合的话，数据库设计师就希望能够实现如下功能。

当用户登陆系统开始一个会话后，数据库系统就建立一张临时表，该表中至少有一个内容，就是用户的帐号(或者该帐号对应的ID)。当有其他用户登陆到系统的时候，系统会先从这张临时表中查询，是否有相同的用户记录。若有的话，就会拒绝用户的登陆，警告用户已经有相同的用户登陆了。当用户正常退出系统或者因为以外情况退出系统结束当前会话的时候，那么数据库系统就会清除这张表的内容。如此，当用户下次登陆系统的话，即使是在意外情况下登陆系统的，也可以正常的登陆，

可见，数据库的临时表在企业实际应用中有着举足轻重的作用。

在ORACLE数据库与SQL SERVER数据库中，都实现了临时表的功能。不过两者实现的方式有差异。而不同的实现方式又赋予了其不同的特点，这是我们在数据库选型中不得不重视的一方面内容。

两个数据库临时表实现方式的异同：

SQL SERVER临时表跟ORACLE数据库临时表的差异，可以利用一句话来概括。SQL SERVER 临时表是在需要用到的时候创建;而ORACLE 数据库的临时表，则是在数据库初始化中就开始创建，在具体的会话或者事务开始后进行操作，结束一个会话或者结束一个事务后该数据库的内容就会被清空。

1、在创建时的异同。

SQL SERVER 数据库的临时表，是在实际需要时创建的。具体的来说，可以利用SELECT语句与CREAT语句创建临时表。如可以利用SELECT * INTO #USER_TEMP FROM USER;通过这条语句就可以在需要的时间创建一张临时表。除此之外，还可以利用CREATE语句，在需要的时候创建临时表。

而ORACLE数据库，是在数据库系统初始化的过程中，就需要建立临时表。也就是在用户安装财务管理软件系统时，初始化数据库系统时，系统就会创建临时表。而不是在临时表需要用到的时候，才被创建。故，ORACLE数据库的临时表创建方式只有一种，在数据库初始化的时候，利用CREATE创建数据库临时表。所以，ORACLE数据库临时表，又有另一种说法。我们一般称ORACLE数据库的临时表是永久性的，只是临时表的内容是临时的，在需要用到临时表时，只要直接调用即可，而不用临时创建。这不像SQL SERVER数据库那样，只有在用到时，才创建该临时表;当结束会话时，不仅表中的数据被清空了，而且该表也被删除了。

笔者评论：