oracle 临时表的使用 [转]

oracle 临时表的使用

跟普通表一样.oracle也可以创建一种临时表用来临时存储一些数据,而跟普通表不同的是,临时表数据的生命周期是有限制的, 要么寄予会话要么寄予事务.事务或会话的结束,oracle都会自动从临时表中删除各自的数据.表中数据只对当前事务或当前会话可见,每个会话都会单独创建一个临时段,维护各自的数据.临时段都是由事务或会话的第一条INSERT语句创建;在临时表上我们还可以通过CREATE INDEX 创建临时索引,而这个索引对表中的所有会话或事务都是有效的,随着临时表的drop,这个索引也会随之drop;临时表还支持truncate语句,但只能运行在寄予会话创建的临时上,truncate当前会话的数据,对其他会话没有影响.

实验

三个会话模拟我的实验,session 1 和session 2 分别操作临时段,session 3监控临时段的使用

1)session 1创建一个临时段

[oracle@neworacle oracle]$ sqlplus mayp/mayp

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Mar 4 15:27:53 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning and Data Mining options

SQL> CREATE GLOBAL TEMPORARY TABLE temp_test(name VARCHAR2(100),age number) ON COMMIT PRESERVE ROWS;

Table created.

SQL>
session 1察看临时表

SQL> select * from temp_test;

no rows selected

临时表为空

2)session 2 登陆

[oracle@neworacle oracle]$ sqlplus mayp/mayp

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Mar 4 15:27:53 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning and Data Mining options

session 2察看临时表

SQL> select * from temp_test;

no rows selected

3) session 1 运行一个insert语句

SQL> insert into temp_test values('111',111);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp_test;

NAME
--------------------------------------------------------------------------------
AGE
----------
111
111
临时表中有一条数据, 这时session 2察看临时表为空

SQL> select * from temp_test;

no rows selected

可以看出数据对于会话的独立性

session 3登陆观察临时段的使用
SQL> select username,session_num,contents,segtype,segfile#,segblk#,extents,blocks,segrfno# from v$tempseg_usage;
USERNAME SESSION_NUM CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------ ----------- ------------------ ------------------ ---------- ---------- ---------- ---------- ----------
MAYP 47976 TEMPORARY DATA 201 2569 1 1280 1

观看到用户mayp 有一个session 47976产生了一个临时段,临时段通过dba_segments是观察不到的,临时段中的数据不是长期保存的,所有不会在dba_segments跟踪维护.

这时session 2 再插入一条数据

SQL> insert into temp_test values('222',222);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp_test;

NAME
--------------------------------------------------------------------------------
AGE
----------
222
222
这是session 2看见了自己插入的数据,不会看见session 1的数据

session 3再观察看看

SQL> select username,session_num,contents,segtype,segfile#,segblk#,extents,blocks,segrfno# from v$tempseg_usage;

USERNAME SESSION_NUM CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------ ----------- ------------------ ------------------ ---------- ---------- ---------- ---------- ----------
MAYP 47976 TEMPORARY DATA 201 2569 1 1280 1
MAYP 23581 TEMPORARY DATA 201 9 1 1280 1

这时存在两个临时段了,每个临时段都包含一个区,第一个段的第一extent从2569块开始分配,第二段的第一个extent从9块开始分配.

4)模拟truncate
因为临时表是寄予session的,所以每个session只能truncate自己的数据

session 2 truncate 表temp_test

SQL> truncate table temp_test;

Table truncated.

SQL> select * from temp_test;

no rows selected

session 2的数据已经清空,看看session 1的select结果

SQL> select * from temp_test;

NAME
--------------------------------------------------------------------------------
AGE
----------
111
111

通过查询发现session 1的数据还在. 所以truncate只对当前的session起作用并不会影响其他会话.

session 3观看临时段的使用

SQL> select username,session_num,contents,segtype,segfile#,segblk#,extents,blocks,segrfno# from v$tempseg_usage;

USERNAME SESSION_NUM CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------ ----------- ------------------ ------------------ ---------- ---------- ---------- ---------- ----------
MAYP 47976 TEMPORARY DATA 201 2569 1 1280 1

通过发现临时段2已经被回收,truncate清空了session 2的数据,系统回收了空间.