`

（转）Oracle临时表使用注意事项

 
阅读更多

http://space.itpub.net/519536/viewspace-701024

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

上一篇 / 下一篇  2011-06-29 23:37:48 / 个人分类：性能优化

查看( 3561 ) / 评论( 13 ) / 评分( 30 / 0 )
  此文将给出在使用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 --
@〇〇
1.物化视图的内容已经补充到文章中，谢谢〇〇；
2.④和⑥本质是一样的。原因就是不同session之间不能互相访问对方的数据，exp对数据库来说就是独立的session。

secooler
〇〇的个人空间引用删除〇〇   /   2011-06-30 11:01:02
再测试一下能否在临时表建物化视图
〇〇的个人空间引用删除〇〇   /   2011-06-30 10:43:53
4和6其实是一回事，因为exp和insert不是一个会话
分享到：
| (转)decode中null相等
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle临时表

    本文讲述了Oracle数据库两种临时表的创建及使用注意事项。

    oracle10g课堂练习I（1）

    目录 前言 1 简介 课程目标 1-2 ...临时表：注意事项 7-43 数据字典：概览 7-44 数据字典视图 7-45 数据字典：用法示例 7-46 小结 7-47 练习概览：管理方案对象 7-48 。。。。

    临时表空间的管理与受损恢复

    　一、临时表空间的特性与注意事项 　1、特性 　用户存储临时数据的表空间 　临时数据通常只在一个数据库会话期间内存在的数据，分为两种形式，排序数据和全局临时表 　临时数据不会被写入存储对象的普通表...

    Oracle数据库管理员技术指南

    1.4.1 配置参数的一些注意事项 1.4.2 建立参数文件的连接 1.4.3 优化数据字典存储 1.5 理解 CREATE DATABASE 命令 1.6 创建数据库的技术 1.6.1 利用 Oracle 安装程序创建数据库 1.6.2 使用安装程序创建数据库...

    Oracle_Database_11g完全参考手册.part3/3

    14.2 使用临时表 14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据：插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-...

    Oracle_Database_11g完全参考手册.part2/3

    14.2 使用临时表 14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据：插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-...

    oracle10g课堂练习I（2）

    临时表：注意事项 7-43 数据字典：概览 7-44 数据字典视图 7-45 数据字典：用法示例 7-46 小结 7-47 练习概览：管理方案对象 7-48 8 管理数据和并发处理 课程目标 8-2 通过 SQL 处理数据 8-3 INSERT 命令 8...

    （E文）基于成本的Oracle优化法则.pdf

    7.3.2 注意事项 156 7.4 “高度均衡”直方图 157 7.5 重新审视数据问题 163 7.5.1 愚蠢的数据类型 163 7.5.2 危险的默认值 166 7.6 本章小结 167 7.7 测试用例 168 第8章 位图索引 169 8.1 入门 170 8.1.1 索引组件 ...

    数据库编程规范

    注意事项 l 索引 每一张表必须有一个主键 &lt;表名&gt;_pk 若干外键索引 &lt;表名&gt;_fk_&lt;数字&gt; 若干其它索引 &lt;表名&gt;_&lt;数字&gt; l 数据类型 定义合适的数据类型,注意 例如： amount number(9,2)...

    asp.net知识库

    .net 2.0 访问Oracle －－与Sql Server的差异，注意事项，常见异常 Ado.net 与NHibernate的关系？ 动态创建数据库 SQL Server数据库安全规划全攻略 .net通用数据库访问组件SQL Artisan应用简介1 在Framework1.0下...

    浪潮ERP账套备份恢复工具

    DBGhost注意事项： 1、受ORACLE数据库系统本身限制，对于ORACLE账套只有本账套恢复时才能恢复操作员密码，即A账套备份出来的数据在B账套上恢复时无法恢复操作员原密码，将重置为默认密码； 2、由于增加备份操作员...

    2009达内SQL学习笔记

    一、注意事项： 大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调式。 “！”在SQL环境下执行Unix命令。 SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用。SQL由多个关键字...

    网管教程 从入门到精通软件篇.txt

    　注意：使用 bootcfg /rebuild 之前，应先通过 bootcfg /copy 命令备份 boot.ini 文件。 　bootcfg /scan　扫描用于 Windows 安装的所有磁盘并显示结果。 　注意：这些结果被静态存储，并用于本次会话。如果在...

Magicbox
Global site tag (gtag.js) - Google Analytics