Oracle中的临时表Temporary Table

2024-09-04 23:44
文章标签 oracle table 临时 temporary

本文主要是介绍Oracle中的临时表Temporary Table,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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

全局临时表(Global Temporary Table)

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

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

创建全局临时表的语法:

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                           24000101 Kochhar                        17000102 De Haan                        17000103 Hunold                          9000104 Ernst                           6000
.................  省略中间行内容 ................205 Higgins                        12008206 Gietz                           8300107 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                           24000101 Kochhar                        17000102 De Haan                        17000103 Hunold                          9000104 Ernst                           6000
.................  省略中间行内容 ................205 Higgins                        12008206 Gietz                           8300107 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                                            会话级

应用场景

全局临时表在Oracle数据库中有广泛的应用场景,包括但不限于:

  • 临时存储计算结果:在执行复杂的查询或计算时,可以将中间结果存储在全局临时表中,以便后续查询或处理。
  • 优化性能:通过减少磁盘I/O操作和提高内存访问速度,全局临时表可以显著提高查询性能。
  • 会话级数据管理:全局临时表可以用于存储用户特定的参数、上下文信息或临时状态,确保数据的隔离性和独立性。

总之,Oracle中的全局临时表是一种强大的工具,它提供了灵活、高效和安全地处理临时数据的机制,满足了各种数据库应用场景的需求。

这篇关于Oracle中的临时表Temporary Table的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1137378

相关文章

Oracle type (自定义类型的使用)

oracle - type   type定义: oracle中自定义数据类型 oracle中有基本的数据类型,如number,varchar2,date,numeric,float....但有时候我们需要特殊的格式, 如将name定义为(firstname,lastname)的形式,我们想把这个作为一个表的一列看待,这时候就要我们自己定义一个数据类型 格式 :create or repla

ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

在win7 64位系统下安装oracle11g,在使用Database configuration Assistant创建数据库时,在创建到85%的时候报错,错误如下: 解决办法: 在listener.ora中增加对BlueAeri-PC或ip地址的侦听,具体步骤如下: 1.启动Net Manager,在“监听程序”--Listener下添加一个地址,主机名写计

Oracle Start With关键字

Oracle Start With关键字 前言 旨在记录一些Oracle使用中遇到的各种各样的问题. 同时希望能帮到和我遇到同样问题的人. Start With (树查询) 问题描述: 在数据库中, 有一种比较常见得 设计模式, 层级结构 设计模式, 具体到 Oracle table中, 字段特点如下: ID, DSC, PID; 三个字段, 分别表示 当前标识的 ID(主键), DSC 当

oracle分页和mysql分页

mysql 分页 --查前5 数据select * from table_name limit 0,5 select * from table_name limit 5 --limit关键字的用法:LIMIT [offset,] rows--offset指定要返回的第一行的偏移量,rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。   oracle 分页 --查前1-9

vue2实践:el-table实现由用户自己控制行数的动态表格

需求 项目中需要提供一个动态表单,如图: 当我点击添加时,便添加一行;点击右边的删除时,便删除这一行。 至少要有一行数据,但是没有上限。 思路 这种每一行的数据固定,但是不定行数的,很容易想到使用el-table来实现,它可以循环读取:data所绑定的数组,来生成行数据,不同的是: 1、table里面的每一个cell,需要放置一个input来支持用户编辑。 2、最后一列放置两个b

ORACLE语法-包(package)、存储过程(procedure)、游标(cursor)以及java对Result结果集的处理

陈科肇 示例: 包规范 CREATE OR REPLACE PACKAGE PACK_WMS_YX IS-- Author : CKZ-- Created : 2015/8/28 9:52:29-- Purpose : 同步数据-- Public type declarations,游标 退休订单TYPE retCursor IS REF CURSOR;-- RETURN vi_co_co

Oracle主键和外键详解及实用技巧

在 Oracle 数据库中,主键(Primary Key)和外键(Foreign Key)用于维护数据库表之间的数据完整性。 1. 主键(Primary Key) 主键是一列或多列,能够唯一标识表中的每一行。表中只能有一个主键,并且主键列不能为空(即 NOT NULL)。 特性: 唯一性:主键中的每一个值都是唯一的,不能重复。非空性:主键列不能包含 NULL 值。索引:Oracle 自动为

分享MSSQL、MySql、Oracle的大数据批量导入方法及编程手法细节

1:MSSQL SQL语法篇: BULK INSERT      [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]         FROM 'data_file'        [ WITH       (      [ [ , ] BATCHSIZE = batch_siz

Oracle start with connect BY 死循环

解决办法 检查start with前有没有where条件, 如果有的话,套一层select,再 Oracle start with connect BY

Java调用Oracle存储过程一

一、通过PL/SQL工具连接上Oracle数据库,创建表zx_test_procedure 二、创建存储过程 ①无返回值的存储过程 存储过程为: create or replace procedure testa (para1 in varchar2,para2 in varchar2) as begin insert into zx_test_procedure(i_id,i_n