关于oracle 临时表的使用

临时表就是用来暂时保存临时数据(亦或叫中间数据)的一个数据库对象,它和普通表有些类似,然而又有很大区别。它只能存储在临时表空间,而非用户的表空间。ORACLE临时表是会话或事务级别的,只对当前会话或事务可见。每个会话只能查看和修改自己的数据。
 
ORACLE临时表有两种类型:会话级的临时表和事务级的临时表。
1)ON COMMIT DELETE ROWS
它是临时表的默认参数,表示临时表中的数据仅在事物过程(Transaction)中有效,当事物提交(COMMIT)后,临时表的暂时段将被自动截断(TRUNCATE),但是临时表的结构 以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后,最好删除临时表,否则数据库会残留很多临时表的表结构和元数据。
2)ON COMMIT PRESERVE ROWS
它表示临时表的内容可以跨事物而存在,不过,当该会话结束时,临时表的暂时段将随着会话的结束而被丢弃,临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后,最好删除临时表,否则数据库会残留很多临时表的表结构和元数据。
1:会话级的临时表的数据和你当前会话有关系,当前SESSION不退出的情况下,临时表中的数据就还存在,临时表的数据只有当你退出当前SESSION的时候才被截断(TRUNCATE TABLE),如下所示:
会话级别的临时表创建:
复制代码
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
    ID NUMBER ,
    NAME VARCHAR2(32)
) ON COMMIT PRESERVE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS
AS
SELECT * FROM TEST;
操作示例:
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 (
   ID NUMBER ,
   NAME VARCHAR2(32)
 ) ON COMMIT PRESERVE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
    SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID         NAME
---------- ----------------
1         kerry
SQL> INSERT INTO TMP_TEST
   SELECT 2, 'rouce' FROM DUAL;
1 row inserted
SQL> ROLLBACK;
Rollback complete
SQL> SELECT * FROM TMP_TEST;
ID           NAME
---------- ----------------------
1           kerry
SQL>
复制代码
 
2:事务级的临时表(默认),这种类型的临时表与事务有关,当进行事务提交或者事务回滚的时候,临时表的数据将自行截断,即当COMMIT或ROLLBACK时,数据就会被TRUNCATE掉,其它的特性和会话级的临时表一致。
事务级临时表的创建方法:
复制代码
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
    ID NUMBER ,
    NAME VARCHAR2(32)
) ON COMMIT DELETE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
     ID NUMBER ,
     NAME VARCHAR2(32)
 ) ON COMMIT DELETE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
   SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> SELECT * FROM TMP_TEST;
ID           NAME
---------- ----------------------
1           kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID             NAME
---------- ------------------------
SQL>
复制代码
 
3:关于临时表只对当前会话或事务可见。每个会话只能查看和修改自己的数据。
用DM用户登录数据库,打开SESSION 1后,创建临时表TMP_TEST
 
复制代码
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
    ID NUMBER ,
    NAME VARCHAR2(32)
) ON COMMIT DELETE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 (
     ID NUMBER ,
     NAME VARCHAR2(32)
 ) ON COMMIT DELETE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
   SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> SELECT * FROM TMP_TEST;
ID           NAME
---------- ---------------------
1 kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID           NAME
---------- -----------------------
SQL>
复制代码

用sys用户登录数据库,打开SESSION 2
SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到临时表数据
SELECT * FROM DM.TMP_TEST; --查不到数据,即使TMP_TEST临时表存在数据。
 
临时表与永久表区别
复制代码
SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",
    "TEMPORARY", DURATION, "MONITORING"
   FROM DBA_TABLES WHERE TABLE_NAME IN ('TMP_TEST', 'TEST') ;
TABLE_NAME   TABLESPACE_NAME LOGGING TEMPORARY DURATION    MONITORING
------------- --------------  ------- --------- ----------- ---------
TEST          TBS_EDS_DATA    YES       N                      YES
TMP_TEST                      NO        Y     SYS$SESSION       NO

没有更多推荐了,返回首页