Oracle中的临时表Temporary Table

zxrhhm

已于 2024-09-04 20:30:35 修改

阅读量1.7k

点赞数 19

文章标签： oracle 数据库

于 2024-09-04 20:22:58 首次发布

本文链接：https://blog.csdn.net/zxrhhm/article/details/141902248

版权

Oracle中的临时表（Temporary Table）是一种特殊类型的表，用于存储临时数据，这些数据在会话结束或事务提交后会自动删除。Oracle数据库提供了两种主要的临时表类型：事务级全局临时表和会话级全局临时表。

全局临时表（Global Temporary Table）

全局临时表是Oracle数据库中常用的临时表类型，它具有以下特点：

临时性：全局临时表中的数据在会话结束或事务提交后（取决于ON COMMIT子句的设置）自动删除。
私有性：尽管名为“全局”，但全局临时表中的数据对于创建它的会话是私有的，其他会话无法访问。
性能优势：全局临时表使用内存或临时表空间存储数据，相比于在磁盘上进行操作，速度更快，可以提高查询性能，并减少数据库资源的占用。
减少锁等待时间：由于全局临时表的数据只在当前会话中存在，不会被其他会话访问，因此减少了锁等待时间，提高了并发访问性能。

创建全局临时表的语法：

CREATE GLOBAL TEMPORARY TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    columnN datatype [constraint]
) ON COMMIT { DELETE ROWS | PRESERVE ROWS };

table_name：临时表的名称。
column1, column2, ..., columnN：表的列名。
datatype：列的数据类型。
constraint：可选的约束条件。
ON COMMIT { DELETE ROWS | PRESERVE ROWS }：指定在事务提交时如何处理临时表中的数据（事务级/会话级）。
DELETE ROWS 是临时表的默认参数,表示在事务提交后删除数据，临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE），但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。
PRESERVE ROWS 它表示临时表的内容可以跨事物而存在，不过，当该会话结束时，临时表的暂时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃，但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。。

会话/事务临时表（概念上的理解）

虽然Oracle官方术语中并不直接称为“会话临时表”，但全局临时表在会话级别的行为可以被视为会话临时表的一种实现。即，全局临时表在会话结束时（如果ON COMMIT设置为PRESERVE ROWS且会话正常结束）或事务提交后（如果ON COMMIT设置为DELETE ROWS）自动删除数据，从而实现了数据的会话级临时性。

示例

-- 全局临时表 使用on commit delete rows选项（事务级临时表，事务提交，数据删除，保留临时比表结构）
HR@orcl> create global temporary table t_temp_emp on commit delete rows as select employee_id,last_name,salary from employees;

Table created.

HR@orcl> select * from t_temp_emp;

no rows selected
-- 全局临时表 使用默认选项（事务级临时表，事务提交，数据删除，保留临时比表结构）
HR@orcl> create global temporary table t_temp_emp_1 as select employee_id,last_name,salary from employees;

Table created.

HR@orcl> select * from t_temp_emp_1;

no rows selected
-- 事务级临时表，事务不提交或回滚，则临时表数据不删除
HR@orcl> insert into t_temp_emp select employee_id,last_name,salary from employees;

107 rows created.

HR@orcl> select * from t_temp_emp;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           24000
        101 Kochhar                        17000
        102 De Haan                        17000
        103 Hunold                          9000
        104 Ernst                           6000
.................  省略中间行内容 ................
        205 Higgins                        12008
        206 Gietz                           8300

107 rows selected.
-- 提交事务，则数据删除
HR@orcl> commit;

Commit complete.

HR@orcl> select * from t_temp_emp;

no rows selected

-- 创建会话级临时表
HR@orcl> create global temporary table t_temp_emp_session on commit preserve rows as select employee_id,last_name,salary from employees;

Table created.
-- 当前会话保留数据
HR@orcl> select * from t_temp_emp_session;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           24000
        101 Kochhar                        17000
        102 De Haan                        17000
        103 Hunold                          9000
        104 Ernst                           6000
.................  省略中间行内容 ................
        205 Higgins                        12008
        206 Gietz                           8300

107 rows selected.
-- 切换会话
HR@orcl> conn / as sysdba
Connected.
-- 再次hr用户登陆
SYS@orcl> conn hr/hr@ORCL
Connected.
-- 发现数据在会话切换(创建临时表的当前会话结束,数据自然也就随之丢弃)
HR@ORCL> select * from t_temp_emp_session;

no rows selected

查看临时表空间中段的情况，可以查看v$temp_extent_map
查看临时表空间的文件：v$tempfile;
查看sql使用临时块的情况：v$tempseg_usage
查看临时块的状态v$tempstat
从DBA_TABLES/USER_TABLES视图的DURATION列来查询是 on commit delete rows / on commit presever rows

HR@ORCL> col table_name format a30
HR@ORCL> select table_name,tablespace_name,DECODE(DURATION,'SYS$SESSION','会话级','SYS$TRANSACTION','事务级') T_TYPE from user_tables where temporary='Y';

TABLE_NAME                     TABLESPACE_NAME                T_TYPE
------------------------------ ------------------------------ ---------
T_TEMP_EMP_1                                                  事务级
T_TEMP_EMP                                                    事务级
T_TEMP_EMP_SESSION                                            会话级