临时表DML操作会产生REDO/UNDO，这个是怎么解释呢？
ORACLE临时表介绍：
ORACLE数据库除了可以保存永久表外，还可以建立临时表temporarytables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。
Oracle临时表分为会话级临时表和事务级临时表。会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。
事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束（commitorrollback），Oracle自动清除临时表中数据。
临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。因此，临时表不需要DML锁。

当一个会话结束(用户正常退出用户不正常退出ORACLE实例崩溃)或者一个事务结束的时候，Oracle对这个会话的表执行TRUNCATE语句清空临时表数据.但不会清空其它会话临时表中的数据.可以索引临时表和在临时表基础上建立视图.同样,建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.临时表可以拥有触发器.



全文的REDO/UNOD大小的单位均为BYTES。

一、环境及用户BYS@bys1>select*fromv$version;

BANNER
--------------------------------------------------------------------------------
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
PL/SQLRelease11.2.0.1.0-Production
CORE11.2.0.1.0Production
TNSforLinux:Version11.2.0.1.0-Production
NLSRTLVersion11.2.0.1.0-Production

BYS@bys1>selectforce_loggingfromv$database;
FOR
---
NO
BYS@bys1>select*fromuser_role_privs;
USERNAMEGRANTED_ROLEADMDEFOS_
---------------------------------------------------------------------
BYSDBANOYESNO
BYS@bys1>select*fromtab;
TNAMETABTYPECLUSTERID
-----------------------------------------------
DEPTTABLE
EMPTABLE
SYS_TEMP_FBTTABLE
#########################################
二、创建一个普通表，并统计建表及插入数据等操作所产生的REDO及UNDO大小注：其中每一步后的查看REDO及UNDO大小我都查询了好几遍，节约篇幅未列出；并且测试系统上只有此客户端在数据库环境中进行操作。
建表前后的REDO/UNDO大小变化
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize1824
undochangevectorsize188
BYS@bys1>createtabletest1asselect*fromdba_objectswhere1=0;
Tablecreated.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize28300
undochangevectorsize7304
插入数据前后的REDO/UNDO大小变化BYS@bys1>insertintotest1select*fromdba_objects;
72470rowscreated.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize8543740
undochangevectorsize342696提交前后的REDO/UNDO大小变化BYS@bys1>commit;
Commitcomplete.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize8543976
undochangevectorsize342696

查询前后的REDO/UNDO大小变化：第一次查询产生REDO是因为延迟块清除：BYS@bys1>setautotraceon
BYS@bys1>selectcount(*)fromtest1;
COUNT(*)
----------
72470
ExecutionPlan
----------------------------------------------------------
Planhashvalue:3896847026
--------------------------------------------------------------------
|Id|Operation|Name|Rows|Cost(%CPU)|Time|
--------------------------------------------------------------------
|0|SELECTSTATEMENT||1|308(1)|00:00:04|
|1|SORTAGGREGATE||1|||
|2|TABLEACCESSFULL|TEST1|83270|308(1)|00:00:04|
--------------------------------------------------------------------
Note
-----
-dynamicsamplingusedforthisstatement(level=2)
Statistics
----------------------------------------------------------
29recursivecalls
1dbblockgets
1165consistentgets
0physicalreads
176redosize
424bytessentviaSQL*Nettoclient
419bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
BYS@bys1>setautotraceoff
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';

NAMEBYTES
--------------------------------------------------------------------------
redosize8547932
undochangevectorsize345332


正常查询并没有产生REDO和UNDOBYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize8550764
undochangevectorsize347644
BYS@bys1>selectcount(*)fromtest1;
COUNT(*)
----------
72470
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize8550764
undochangevectorsize347644


统计情况如下：createtabletest1asselect*fromdba_objectswhere1=0;语句：产生REDO：26476，UNDO：7116
insertintotest1select*fromdba_objects;语句：产生REDO：826070，UNDO：335392
COMMIT语句：产生REDO/UNDO分别为：236和0


三、表创建一个ONCOMMITDELETEROWS临时表，并统计建表及插入数据等操作所产生的REDO及UNDO大小在上一步做完后退出SQLPLUS再登陆进行操作。

建表前后的REDO/UNDO大小变化BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize1824
undochangevectorsize188
BYS@bys1>createglobaltemporarytabletemp1oncommitdeleterowsasselect*fromdba_objectswhere1=0;
Tablecreated.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';

NAMEBYTES
--------------------------------------------------------------------------
redosize27648
undochangevectorsize7096
插入数据前后的REDO/UNDO大小变化BYS@bys1>insertintotemp1select*fromdba_objects;

72471rowscreated.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize478256
undochangevectorsize325332
BYS@bys1>selectcount(*)fromtemp1;

COUNT(*)
----------
72471
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize478256
undochangevectorsize325332提交前后的REDO/UNDO大小变化BYS@bys1>commit;
Commitcomplete.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize478464
undochangevectorsize325332查询前后的REDO/UNDO大小变化：--无变化BYS@bys1>selectcount(*)fromtemp1;

COUNT(*)
----------
0
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';

NAMEBYTES
--------------------------------------------------------------------------
redosize478464
undochangevectorsize325332统计情况如下：createglobaltemporarytabletemp1语句：产生REDO和UNDO分别为：258246908
insertintotemp1select*fromdba_objects;语句：产生REDO和UNDO分别为：450608318436
COMMIT语句：产生REDO/UNDO分别为：208和0

四、表创建一个PRESERVEROWS临时表中的数据基于会话临时表，并统计建表及插入数据等操作所产生的REDO及UNDO大小在上一步做完后退出SQLPLUS再登陆进行操作。

建表前后的REDO/UNDO大小变化BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize1824
undochangevectorsize188
BYS@bys1>createglobaltemporarytabletemp2oncommitpreserverowsasselect*fromdba_objectswhere1=0;
Tablecreated.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize27924
undochangevectorsize7180

插入数据前后的REDO/UNDO大小变化BYS@bys1>insertintotemp2select*fromdba_objects;
72472rowscreated.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize478988
undochangevectorsize325792
BYS@bys1>selectcount(*)fromtemp2;
COUNT(*)
----------
72472
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize478988
undochangevectorsize325792提交前后的REDO/UNDO大小变化BYS@bys1>commit;
Commitcomplete.
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize479224
undochangevectorsize325792
查询前后的REDO/UNDO大小变化：--无变化BYS@bys1>selectcount(*)fromtemp2;

COUNT(*)
----------
72472BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize479224
undochangevectorsize325792
测试基于会话的临时表，退出会话再登陆查询，临时表内无数据。BYS@bys1>exit
DisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
[oracle@bys001~]$sqlplusbys/bys@bys1
SQL*Plus:Release11.2.0.1.0ProductiononMonSep1619:17:062013
Copyright(c)1982,2009,Oracle.Allrightsreserved.
Connectedto:
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
BYS@bys1>selectcount(*)fromtemp2;
COUNT(*)
----------
0
BYS@bys1>selectname,valueasbytesfrom(selectb.name,a.valuefromv$mystata,v$statnamebwherea.STATISTIC#=b.statistic#)wherename='redosize'ornamelike'undochange%';
NAMEBYTES
--------------------------------------------------------------------------
redosize1780
undochangevectorsize188

统计情况如下：createglobaltemporarytabletemp2语句：产生REDO和UNDO分别为：261006992
insertintotemp2select*fromdba_objects;语句：产生REDO和UNDO分别为：451064318612
COMMIT语句：产生REDO/UNDO分别为：236和0

五：各个操作产生的REDO/UNDO大小对比

普通表统计情况如下：createtabletest1asselect*fromdba_objectswhere1=0;语句：产生REDO和UNDO分别为：26476，7116
insertintotest1select*fromdba_objects;语句：产生REDO和UNDO分别为：826070，335392
COMMIT语句：产生REDO/UNDO分别为：236和0

ONCOMMITDELETEROWS临时表统计情况如下：
createglobaltemporarytabletemp1语句：产生REDO和UNDO分别为：25824，6908
insertintotemp1select*fromdba_objects;语句：产生REDO和UNDO分别为：450608，318436
COMMIT语句：产生REDO/UNDO分别为：208和0

PRESERVEROWS临时表统计情况如下：
createglobaltemporarytabletemp2语句：产生REDO和UNDO分别为：26100，6992
insertintotemp2select*fromdba_objects;语句：产生REDO和UNDO分别为：451064，318612
COMMIT语句：产生REDO/UNDO分别为：236和0