oracle数据库临时表的属性及用法

临时表的类别

临时表分为两种：事物型临时表——在事物完成后会立即truncate表，会话型临时表——在事物完成后会暂时保存数据，在会话中止或者显示提交truncate等语句才会删除数据。

ON COMMIT Setting	Implications
DELETE ROWS	This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.
PRESERVE ROWS	This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session. The database truncates the table when you terminate the session.

 

创建语句

创建临时表的语法如下

CREATE GLOBAL TEMPORARY TABLE temp_tab_name

([column datatype [,column datatype])

[ON COMMIT {DELETE | PRESERVE} ROWS]

[TALBESPACE  tbs];

在默认情况下，临时表存在与默认临时表空间中，也可以指定到永久表空间中或者创建一个新的临时表空间，把临时表指定到这个新临时表空间中。前面的博文已经讲诉了创建另一个临时表空间，这里就不再赘述。

我们使用SCOTT用户创建一个事物临时表和会话临时表

CREATE GLOBAL TEMPORARY TABLE temp_trans

        (startdate DATE,

         class CHAR(20))

      ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE temp_sess

        (startdate DATE,

         class CHAR(20))

      ON COMMIT PRESERVE  ROWS;

在创建完临时表后，查看表的属性

 

SQL> show user;

User is "scott"

SQL> select table_name,TEMPORARY,DURATION,LOGGING from user_tables where table_name like 'TEMP_%';

TABLE_NAME                     TEMPORARY DURATION        LOGGING

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

TEMP_SESS                      Y         SYS$SESSION     NO

TEMP_TRANS                     Y         SYS$TRANSACTION NO

分别给2个表插入数据

SQL> insert into temp_trans values(sysdate,'transaction');

1 row inserted

SQL> insert into temp_sess values(sysdate,'session');

1 row inserted

SQL> select * from temp_trans;

STARTDATE   CLASS

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

2017-6-15 上 transaction

SQL> select * from temp_sess;

STARTDATE   CLASS

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

2017-6-15 上 session

SQL> commit;

Commit complete

SQL> select * from temp_trans;

STARTDATE   CLASS

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

SQL> select * from temp_sess;

STARTDATE   CLASS

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

2017-6-15 上 session

以上例子可以看出，事物临时表在事物提交后表内的数据不会保存，而会话临时表在事物提交后表内的数据会保存。

sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 10:52:31 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select * from temp_sess;     

no rows selected

用其他会话登录SCOTT，便看不到表内的数据

SQL> drop table temp_sess;

drop table temp_sess

           *

ERROR at line 1:

ORA-14452: attempt to create, alter or drop an index on temporary table already

in use

SQL> truncate table temp_sess;

Table truncated.

SQL> drop table temp_sess;

Table dropped.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.

在删除表时，如果表内有数据，且session存在，是无法删除的。

使用场景

在一般情况下，临时表的数据存在于各自的PGA会话中，不涉及磁盘活动，所以针对临时表的访问是比放在永久表空间中的表快。临时表的DML语句不会生成重做数据，也没有这个必要。临时表也可以创建索引，其索引跟临时表属性一致，临时表是事物型索引就是事物型，临时表是会话型索引就是会话型。

如果表数据的结果集希望被存储在内存中，在会话期间表的行记录能被保存在内存中，会话结束后能被删除数据的表使用临时表就是非常合适的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31461640/viewspace-2140806/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31461640/viewspace-2140806/