OracleDBA之路Manager Table（二）

临时表管理

create tablespace assm datafile '/u01/oradata/houzhh/assm01.dbf' size 20M extent management local uniform size 128k segment space management auto;

Tablespace created.

 

create user assm identified by assm default tablespace assm;

User created.

 

grant connect,resource to assm;

Grant succeeded.

 

 conn assm/assm

Connected.

 show user

USER is "ASSM"
 

创建一个普通表

create table t(id int,name varchar2(20));
insert into t values(1,'houzhh');
insert into t values(2,'suiying');
insert into t values(3,'mr.hou');
commit;

创建一个session级别的临时表

create global temporary table tem_table_session on commit preserve rows as select * from assm.t where 1=0;

创建一个transaction级别的临时表

create global temporary table tem_table_transaction on commit delete rows as select * from assm.t where 1=0;

 分别向2个临时表中插入数据

insert into tem_table_session select * from assm.t;

insert into tem_table_transaction select * from assm.t;

 

查看2个临时表中的数据记录数

select session_cnt,transaction_cnt from(select count(*) session_cnt from tem_table_session),(select count(*) transaction_cnt from tem_table_transaction);

 

SESSION_CNT TRANSACTION_CNT
----------- ---------------
          3               3

 

提交

commit；

再次查看2个临时表数据记录数

select session_cnt,transaction_cnt from(select count(*) session_cnt from tem_table_session),(select count(*) transaction_cnt from tem_table_transaction);

 

SESSION_CNT TRANSACTION_CNT
----------- ---------------
          3               0

看不到基于事物的临时表数据个数；

退出会话

disconnect

connect assm/assm

select session_cnt,transaction_cnt from(select count(*) session_cnt from tem_table_session),(select count(*) transaction_cnt from tem_table_transaction);

SESSION_CNT TRANSACTION_CNT
----------- ---------------
          0               0

 

 

备注：

on commit preserve rows 使得该临时表处于session级别 commit后还可以看到，在会话断开之前，数据一致存在临时表中。

on commit delete rows 使得该临时表处于transaction级别 commit后就看不到了