oracle临时表及临时表空间

时间:2022-01-11 18:13:06

查看临时表空间中段的情况,可以查看v$temp_extent_map

查看临时表空间的文件:v$tempfile;

查看sql使用临时块的情况:v$tempseg_usage

查看临时块的状态v$tempstat

oracle中的临时表是持有会话私有数据的(就是说2个用户名相同的session使用一个临时表,互相间也看不到彼此的数据),数据保留的长度有会话或事务两种。对于事务规格的表数据在事务期间存在,对于会话规格的表数据在会话期间可见。

在临时表上使用truncate命令,删除掉的是自己会话的数据,不会影响使用相同临时表的别的会话的数据。

在临时表上可以创建索引,索引也是临时的,索引上的数据与临时表有相同的会话或事务范围。可以使用oracle的工具导出临时表的定义,但是没有办法导出它的数据。你可以复制临时表的定义,但是不能复制它的数据。临时表是使用的临时段,当第一次insert的时候会分配段,创建的时候不分配。对于事务的临时表,临时段在事务结束后回收,对于会话的临时表,临时段在会话结束后回收。

oracle中的临时段

在处理查询的时候,oracle对于sql中间步骤的处理经常回去查询临时段。典型的排序的时候会用临时段,如果能在内存中完成排序操作或能使用索引完成操作,那么是不会创建临时段的。Oracle会在第一次执行磁盘排序时创建排序段,并且根据需要扩展,但是不会收缩。下面的语句会使用临时段。

  • CREATE INDEX

  • SELECT ... ORDER BY

  • SELECT DISTINCT ...

  • SELECT ... GROUP BY

  • SELECT . . . UNION

  • SELECT ... INTERSECT

  • SELECT ... MINUS


对于查询,如果用户没有分配默认的临时表空间,默认的而是system表空间。

全局临时表的数据获取是基于两个生命周期:
1 会话on commit preserve rows
提交时保留行,表示事务提交,行不受影响。是基于会话的。
2事务on commit delete rows
提交时删除行,表示事务提交,行就没了,是基于事务的。




SQL> create global temporary table sys_event_session on commit preserve rows as select *  from v$system_event where 1=0;


表已创建。


SQL> insert into sys_event_session select * from v$system_event;


已创建49行。


SQL> commit;


提交完成。


SQL> select count(*) from sys_event_session;


  COUNT(*)
----------
        49


SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开


C:\Users\Administrator>sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 3月 7 00:27:28 2014


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


请输入用户名:  baixyu
输入口令:


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


SQL> select count(*) from sys_event_session;


  COUNT(*)
----------
         0


SQL>



SQL> create global temporary table sys_event_transaction on commit delete rows as select * from v$system_event where 1=0;


表已创建。


SQL> insert into sys_event_transaction select * from v$system_event;


已创建49行。


SQL> select count(*) from sys_event_transaction;


  COUNT(*)
----------
        49


SQL> commit;


提交完成。


SQL> select count(*) from sys_event_transaction;


  COUNT(*)
----------
         0

临时表是用的临时段,但是在user_tables视图中的tablespace_name中也不是临时表空间的名称
SQL> select table_name,tablespace_name from user_tables where table_name=upper('sys_event_session');


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SYS_EVENT_SESSION


select username,sql_id from v$tempseg_usage;


select sql_text from v$sql where sql_id='9m7787camwh4m';

begin :id := sys.dbms_transaction.local_transaction_id; end; 


无法查看到具体执行的sql是什么样的。

下面测试下dg中临时表的情况

经过测试在主库上创建临时表是没有问题的,但是临时表中的数据不会同步到从上。