【TEMPORARY TABLE】Oracle临时表使用注意事项

2023-06-06 04:30

短信预约 -IT技能 免费直播动态提醒

此文将给出在使用Oracle临时表的过程中需要注意的事项，并对这些特点进行验证。
①临时表不支持物化视图
②可以在临时表上创建索引
③可以基于临时表创建视图
④临时表结构可被导出，但内容不可以被导出
⑤临时表通常是创建在用户的临时表空间中的，不同用户可以有自己的独立的临时表空间
⑥不同的session不可以互相访问对方的临时表数据
⑦临时表数据将不会上DML（Data Manipulation Language）锁

1.临时表不支持物化视图
1）环境准备
（1）创建基于会话的临时表
sec@ora10g> create global temporary table t_temp_session (x int) on commit preserve rows;

Table created.

sec@ora10g> col TABLE_NAME for a30
sec@ora10g> col TEMPORARY for a10
sec@ora10g> select TABLE_NAME,TEMPORARY from user_tables where table_name = 'T_TEMP_SESSION';

TABLE_NAME                     TEMPORARY
------------------------------ ----------
T_TEMP_SESSION                 Y

（2）初始化两条数据
sec@ora10g> insert into t_temp_session values (1);

1 row created.

sec@ora10g> insert into t_temp_session values (2);

1 row created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t_temp_session;

         X
----------
         1
         2

（3）在临时表T_TEMP_SESSION上添加主键
sec@ora10g> alter table T_TEMP_SESSION add constraint PK_T_TEMP_SESSION primary key(x);

Table altered.

2）在临时表T_TEMP_SESSION上创建物化视图
（1）创建物化视图日志日志
sec@ora10g> create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values;
create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

可见，在创建物化视图时便提示，临时表上无法创建物化视图日志。

（2）创建物化视图
sec@ora10g> create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION;
create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION
                                                                                                                        *
ERROR at line 1:
ORA-23413: table "SEC"."T_TEMP_SESSION" does not have a materialized view log

由于物化视图日志没有创建成功，因此显然物化视图亦无法创建。

2.在临时表上创建索引
sec@ora10g> create index i_t_temp_session on t_temp_session (x);

Index created.

临时表上索引创建成功。

3.基于临时表创建视图
sec@ora10g> create view v_t_temp_session as select * from t_temp_session where x<100;

View created.

基于临时表的视图创建成功。

4.临时表结构可被导出，但内容不可以被导出
1）使用exp工具备份临时表
ora10g@secdb /home/oracle$ exp sec/sec file=t_temp_session.dmp log=t_temp_session.log tables=t_temp_session

Export: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:43 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                 T_TEMP_SESSION
Export terminated successfully without warnings.

可见在备份过程中，没有显示有数据被导出。

2）使用imp工具的show选项查看备份介质中的SQL内容
ora10g@secdb /home/oracle$ imp sec/sec file=t_temp_session.dmp full=y show=y

Import: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:57 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
. importing SEC's objects into SEC
"CREATE GLOBAL TEMPORARY TABLE "T_TEMP_SESSION" ("X" NUMBER(*,0)) ON COMMIT "
"PRESERVE ROWS "
"CREATE INDEX "I_T_TEMP_SESSION" ON "T_TEMP_SESSION" ("X" ) "
Import terminated successfully without warnings.

这里体现了创建临时表和索引的语句，因此临时表的结构数据是可以被导出的。

3）尝试导入数据
ora10g@secdb /home/oracle$ imp sec/sec file=t_temp_session.dmp full=y ignore=y

Import: Release 10.2.0.1.0 - Production on Wed Jun 29 22:07:16 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
. importing SEC's objects into SEC
Import terminated successfully without warnings.

依然显示没有记录被导入。

5.查看临时表空间的使用情况
可以通过查询V$SORT_USAGE视图获得相关信息。
sec@ora10g> select username,tablespace,session_num sid,sqladdr,sqlhash,segtype,extents,blocks from v$sort_usage;

USERNAME TABLESPACE     SID SQLADDR     SQLHASH SEGTYPE EXTENTS BLOCKS
-------- ---------- ------- -------- ---------- ------- ------- -------
SEC      TEMP           370 389AEC58 1029988163 DATA          1     128
SEC      TEMP           370 389AEC58 1029988163 INDEX         1     128

可见SEC用户中创建的临时表以及其上的索引均存放在TEMP临时表空间中。
在创建用户的时候，可以指定用户的默认临时表空间，这样不同用户在创建临时表的时候便可以使用各自的临时表空间，互不干扰。

6.不同的session不可以互相访问对方的临时表数据
1）在第一个session中查看临时表数据
sec@ora10g> select * from t_temp_session;

         X
----------
         1
         2

此数据为初始化环境时候插入的数据。

2）在单独开启一个session，查看临时表数据。
ora10g@secdb /home/oracle$ sqlplus sec/sec

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 29 22:30:05 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sec@ora10g> select * from t_temp_session;

no rows selected

说明不同的session拥有各自独立的临时表操作特点，不同的session之间是不能互相访问数据。

7.临时表数据将不会上DML（Data Manipulation Language）锁
1）在新session中查看SEC用户下锁信息
sec@ora10g> col username for a8
sec@ora10g> select
2       b.username,
3       a.sid,
4       b.serial#,
5       a.type "lock type",
6       a.id1,
7       a.id2,
8       a.lmode
9 from v$lock a, v$session b
10 where a.sid=b.sid and b.username = 'SEC'
11 order by username,a.sid,serial#,a.type;

no rows selected

不存在任何锁信息。

2）向临时表中插入数据，查看锁信息
（1）插入数据
sec@ora10g> insert into t_temp_session values (1);

1 row created.

（2）查看锁信息
sec@ora10g> select
2       b.username,
3       a.sid,
4       b.serial#,
5       a.type "lock type",
6       a.id1,
7       a.id2,
8       a.lmode
9 from v$lock a, v$session b
10 where a.sid=b.sid and b.username = 'SEC'
11 order by username,a.sid,serial#,a.type;

                               lock                                lock
USERNAME        SID    SERIAL# type           id1         id2      mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC             142        425 TO           12125           1         3
SEC             142        425 TX           65554         446         6

此时出现TO和TX类型锁。

（3）提交数据后再次查看锁信息
sec@ora10g> commit;

Commit complete.

sec@ora10g> select
2       b.username,
3       a.sid,
4       b.serial#,
5       a.type "lock type",
6       a.id1,
7       a.id2,
8       a.lmode
9 from v$lock a, v$session b
10 where a.sid=b.sid and b.username = 'SEC'
11 order by username,a.sid,serial#,a.type;

                               lock                                lock
USERNAME        SID    SERIAL# type           id1         id2      mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC             142        425 TO           12125           1         3

事务所TX被释放。TO锁保留。

3）测试更新数据场景下锁信息变化
（1）更新临时表数据
sec@ora10g> update t_temp_session set x=100;

1 row updated.

（2）锁信息如下
                               lock                                lock
USERNAME        SID    SERIAL# type           id1         id2      mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC             142        425 TO           12125           1         3
SEC             142        425 TX          524317         464         6

（3）提交数据
sec@ora10g> commit;

Commit complete.

（4）锁信息情况
                               lock                                lock
USERNAME        SID    SERIAL# type           id1         id2      mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC             142        425 TO           12125           1         3

4）测试删除数据场景下锁信息变化
（1）删除临时表数据
sec@ora10g> delete from t_temp_session;

1 row deleted.

（2）查看锁信息
                               lock                                lock
USERNAME        SID    SERIAL# type           id1         id2      mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC             142        425 TO           12125           1         3
SEC             142        425 TX          327713         462         6

（3）提交数据
sec@ora10g> commit;

Commit complete.

（4）锁信息情况
                               lock                                lock
USERNAME        SID    SERIAL# type           id1         id2      mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC             142        425 TO           12125           1         3

5）总结
在临时表上的增删改等DML操作都会产生TO锁和TX事务所。TO锁会从插入数据开始一直存在。
但整个过程中都不会产生DML的TM级别锁。

8.小结
本文就临时表使用过程中常见的问题和特点进行了介绍。临时表作为Oracle的数据库对象，如果能够在理解这些特性基础上加以利用将会极大地改善系统性能。

Good luck.

secooler
11.06.29

-- The End --

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉

【TEMPORARY TABLE】Oracle临时表使用注意事项

下载Word文档到电脑，方便收藏和打印～

下载Word文档

编程热搜

Python 学习之路 - Python
一、安装Python34Windows在Python官网（https://www.python.org/downloads/）下载安装包并安装。Python的默认安装路径是：C:\Python34配置环境变量：【右键计算机】--》【属性】-
chatgpt的中文全称是什么
chatgpt的中文全称是生成型预训练变换模型。ChatGPT是什么ChatGPT是美国人工智能研究实验室OpenAI开发的一种全新聊天机器人模型，它能够通过学习和理解人类的语言来进行对话，还能根据聊天的上下文进行互动，并协助人类完成一系列
C/C++中extern函数使用详解
C/C++可变参数的使用
可变参数的使用方法远远不止以下几种，不过在C,C++中使用可变参数时要小心，在使用printf()等函数时传入的参数个数一定不能比前面的格式化字符串中的’%’符号个数少，否则会产生访问越界，运气不好的话还会导致程序崩溃
css样式文件该放在哪里
php中数组下标必须是连续的吗
Python 3 教程
Python 3 教程 Python 的 3.0 版本，常被称为 Python 3000，或简称 Py3k。相对于 Python 的早期版本，这是一个较大的升级。为了不带入过多的累赘，Python 3.0 在设计的时候没有考虑向下兼容。 Python
Python pip包管理
一、前言在Python中，安装第三方模块是通过 setuptools 这个工具完成的。 Python有两个封装了 setuptools的包管理工具： easy_install 和 pip ，目前官方推荐使用 pip。
ubuntu如何重新编译内核
改善Java代码之慎用java动态编译