[20180104]oracle临时表.txt
--//简单探究oracle临时表,oracle对于临时表日志记录相对普通表DML操作日志量要少,因为临时表dml操作不需要记录后映像,仅仅为了rollback操作,
--//仅仅在日志文件中记录undo产生的日志以及少量递归事务.这样日志相对普通表事务要小一些,但是对于delete操作,因为日志记录整条记录,产生
--//日志相对较大.
通过Dump UNDO Block观察到DML操作记录在UNDO中的信息，主要为以下内容：
1、对于Insert操作，需要在UNDO中记录插入行的ROWID.
2、对于Update操作，需要在UNDO中记录被更新列的前镜像的值，同时也会记录被更新行的ROWID。
3、对于Delete操作，需要在UNDO中记录被删除行所有列的值(前镜像)及ROWID。
--//本文通过测试例子简单说明这些问题.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create global temporary table t(id number,test varchar2(20),pad varchar2(20)) on commit preserve rows;
SYS@book> alter system archive log current ;
System altered.
SCOTT@book> @ &r/logfile ;
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ------ ------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- -------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 746 52428800 512 1 NO CURRENT 13277158952 2018-01-04 15:45:35 2.814750E+14
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 744 52428800 512 1 YES INACTIVE 13277158937 2018-01-04 15:45:30 13277158943 2018-01-04 15:45:31
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 745 52428800 512 1 YES ACTIVE 13277158943 2018-01-04 15:45:31 13277158952 2018-01-04 15:45:35
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//当前日志是/mnt/ramdisk/book/redo01.log.
2.测试1:
--//插入记录:
insert into t values (1,'a1b2c3d4','aaaaaaa');
commit ;
alter system checkpoint;
alter system checkpoint;
$ strings /mnt/ramdisk/book/redo01.log |egrep "a1b2c3d4|aaaaaaa"
--//无法查询到插入字符串a1b2c3d4,aaaaaa.因为产生的redo仅仅记录undo段产生的日志(对于临时表),这样对应插入操作,仅仅需要知道rowid就足够rollback.
--//所以在日志文件看不到插入的字符串信息.
3.测试2:
--//修改记录:
SCOTT@book> select * from t;
ID TEST PAD
------------ -------------------- --------------------
1 a1b2c3d4 aaaaaaa
update t set test=upper(test) where id=1 and rownum=1;
commit ;
alter system checkpoint;
$ strings -t x /mnt/ramdisk/book/redo01.log |egrep -i "a1b2c3d4|aaaaaaa"
18559 a1b2c3d4
--//仅仅看到小写的字符串a1b2c3d4,说明产生日志部分仅仅记录undo产生的前映像,为了rollback的需要,而后映像没有记录.而且oracle日志记录的是改变向量,
--//这样日志里面看到字符串a1b2c3d4.
--//对于update操作临时表仅仅记录undo段产生的日志,这样看到的信息仅仅是修改前的前映像记录向量.
4.测试3:
SCOTT@book> select * from t;
ID TEST PAD
------------ -------------------- --------------------
1 A1B2C3D4 aaaaaaa
delete from t where id=1 and rownum=1;
commit ;
alter system checkpoint;
$ strings -t x /mnt/ramdisk/book/redo01.log |egrep -i "a1b2c3d4|aaaaaaa"
18559 a1b2c3d4
4056e YMA1B2C3D4aaaaaaa*4
--//可以发现记录的整条记录.对于临时delete操作产生的日志最大,这样在实际应用中需要这个细节.
5.测试4.做一个对比说明:
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 746 52428800 512 1 YES ACTIVE 13277158952 2018-01-04 15:45:35 13277160333 2018-01-04 16:03:34
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 747 52428800 512 1 NO CURRENT 13277160333 2018-01-04 16:03:34 2.814750E+14
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 745 52428800 512 1 YES INACTIVE 13277158943 2018-01-04 15:45:31 13277158952 2018-01-04 15:45:35
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//当前日志是/mnt/ramdisk/book/redo02.log.
create table tx(id number,test varchar2(20),pad varchar2(20)) ;
insert into tx values (1,'AAAA1234','BBBBBB');
commit ;
$ strings -t x /mnt/ramdisk/book/redo02.log |egrep "AAAA1234|BBBBBB"
ca64 AAAA1234BBBBBB
--//可以发现对于普通表的插入操作,记录后映像,可以看到插入的信息AAAA1234,BBBBBB.
--//12C提供特性临时表执行dml时生成的undo保存在临时表空间，这个特性叫Temporary Undo，由数据库参数temp_undo_enabled控制.这样以上问题不再存在.
