oracle value 临时数据,oracle中的临时表

oracle中的临时表

临时表通常用来保存一个事务或者会话期间的数据.

临时表中保存的数据是具有独立性的,只对各自会话可见,并且每个会话

都只能查询和修改属于此会话的数据,在对temporary table作dml操作时,

不需要申请锁资源,因此lock语句对于临时表来说是没有作用的.

在空间方面,在创建永久性表时通常是需要为表分配initial extent,但是对于

临时表是不需要的,临时表只是在使用的时候,根据数据来分配创建临时段.

对临时表的 DML 操作不会产生数据修改的重做日志,但是将产生被修改数据的撤销记录，

及撤销记录的重做日志.

我们来看一下临时表所产生的redo size情况.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

SID STATISTIC#      VALUE

---------- ---------- ----------

159        134     929956

SQL> create global temporary table temp_ses on

2  commit preserve rows

3  as

4  select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

SID STATISTIC#      VALUE

---------- ---------- ----------

159        134     948420

再来看一下创建一个同样大小数据量的永久性表:

SQL> select * from v$sesstat where sid=142 and statistic#=134;

SID STATISTIC#      VALUE

---------- ---------- ----------

142        134       1432

SQL> create table pert as select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=142 and statistic#=134;

SID STATISTIC#      VALUE

---------- ---------- ----------

142        134    5724360

SQL> select 5724360-1432 from dual;

5724360-1432

------------

5722928

SQL>

可以看到两者的差距已经不仅仅是一个数据级了.

临时表一共有两种:

会话级别和事务级别的.

先来看一个会话级别的.

SQL> create global temporary table tmp_ses on commit preserve rows

2  as  select * from dba_objects;

Table created.

SQL>

on commit preserve rows表明这一个基于会话的临时表,在会话断开以后.

所有数据都将被抹去.

SQL> create global temporary table tmp_trans on commit delete rows

2  as select * from dba_objects;

Table created.

SQL>

on commit delete rows表示这是一个基于事务的临时表，在会话提交的时候，数据

就会被自动清除掉。

SQL> insert into tmp_trans select * from dba_objects;

49772 rows created.

SQL> select count(*) from tmp_trans;

COUNT(*)

----------

49772

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_trans;

COUNT(*)

----------

0

这里可以看到在事务commit以后，临时表中的数据被全部清空。而这个清空的过程

是几乎不存在开销，oracle完成的仅仅是把临时段回收的一个动作。

临时段的分配

临时表使用临时段来分配数据，因此在创建临时表的时候，oracle并不会为其分配段，

而是在使用的时候才分配。我们可以使用v$sort_usage来观察某个临时表所占用的

空间大小。

SQL> select * from v$sort_usage;

no rows selected

SQL> insert into tmp_ses select * from dba_objects;

49772 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

COUNT(*)

----------

49772

SQL> select distinct sid from v$mystat;

SID

----------

142

SQL> select sid,serial# from v$session where sid=142;

SID    SERIAL#

---------- ----------

142        168

SQL>  select username,user,session_num,

2  tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS

---------- ---------- ----------- ---------- ---------- ---------- ----------

TEST       TEST               168 TEMP              201       2313        768

这里可以看到，通过与v$session视图中的serial#相关联，可以得出某个会话的

所拥有的临时表中的数据的大小，当然，这里与永久表一样，在delete的时候

是不会释放出空间的：

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS

---------- ---------- ----------- ---------- ---------- ---------- ----------

TEST       TEST               168 TEMP              201       2313        768

SQL> delete tmp_ses;

19772 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

COUNT(*)

----------

0

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS

---------- ---------- ----------- ---------- ---------- ---------- ----------

TEST       TEST               168 TEMP              201       2313        768

SQL>

SQL> truncate table tmp_ses;

Table truncated.

SQL>  select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

no rows selected

可以看到，在删除数据时，oracle采用了节省成本的方式，减少了不必要的开销。

关于临时表的事务，与事务相关的临时表中的数据可以被用户的事务及子事务访问。

但是这些数据不能被同一会话里的两个事务同时访问。不同会话中的事务可以同时

使用同一个事务相关的临时表。如果用户事务对临时表执行了 INSERT 操作，

在此之后此事务的子事务将不能使用这个临时表。

如果在子事务中对临时表执行了 INSERT 操作，临时表中已有的数据将被清除。

子事务结束后，父事务及其他子事务对此临时表访问权利将被恢复。