目录
定义
on commit delete rows 事务级临时表
on commit preserve rows 会话级临时表
删除临时表
定义
临时表是用来保留临时或者中间数据的表，属于数据哭对象，有对应编号
它可以像普通表一样使用，并为每个会话提供专有数据，会话之间不受影响
临时表的数据会在会话完成或事物结束时自动清除数据
它存在于temp表空间内并不占用用户表空间
对于临时表的操作，不生成日志和回滚数据，没有锁，不备份，不维护
on commit delete rows 事务级临时表
在用户提交(commit)时清除数据，也叫事务级临时表
由sys用户创建全局临时表
SQL> create global temporary table tb_temp01 on commit delete rows as select * from scott.salgrade;
Table created.
SQL> select * from tb_temp01;
no rows selectedcreate 属于ddl会隐式提交，因此表中没有数据
在该会话的临时表中添加数据
SQL> insert into tb_temp01 select * from scott.salgrade;
5 rows created.
SQL> select * from tb_temp01;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999新建一个会话查看临时表中的内容
SQL> show user
USER is "SYS"
SQL> select * from tb_temp01;
no rows selected看不到数据，因为临时表的数据仅能在当前会话中看到
在原来的会话中提交数据看一下
SQL> select * from tb_temp01;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> commit;
Commit complete.
SQL> select * from tb_temp01;
no rows selected提交数据后，反而看不到数据，这是因为 on commit delete rows的限制
on commit preserve rows 会话级临时表
此关键字意为当前会话结束时，丢弃数据，也叫会话级临时表
还是用sys用户建立会话集临时表
SQL> create global temporary table tb_temp02 on commit preserve rows as select * from scott.salgrade;
Table created.
SQL> select * from tb_temp02;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999会话级临时表在ddl之后依旧保留数据
那么在另一个会话中能不能看到数据呢
SQL> conn / as sysdba
Connected.
SQL> select * from tb_temp02;
no rows selected显然不能
在前一个会话中对表进行增删改等dml操作并提交
SQL> insert into tb_temp02 values(9,9999,9999);
1 row created.
1 row deleted.
SQL> update tb_temp02 set grade=88 where grade=4;
1 row updated.
SQL> select * from tb_temp02;
GRADE LOSAL HISAL
---------- ---------- ----------
2 1201 1400
3 1401 2000
88 2001 3000
5 3001 9999
9 9999 9999
SQL> commit;
Commit complete.
SQL> select * from tb_temp02;
GRADE LOSAL HISAL
---------- ---------- ----------
2 1201 1400
3 1401 2000
88 2001 3000
5 3001 9999
9 9999 9999数据依然存在
结束会话或切换用户之后数据消失
SQL> select * from tb_temp02;
GRADE LOSAL HISAL
---------- ---------- ----------
2 1201 1400
3 1401 2000
88 2001 3000
5 3001 9999
9 9999 9999
SQL> conn tiger/scott;
Connected.
SQL> conn / as sysdba
Connected.
SQL> select * from tb_temp02;
no rows selected
删除临时表
事物级临时表可以直接用drop语句删除
SQL> select * from tb_temp01;
no rows selected
SQL> insert into tb_temp01 select * from scott.salgrade;
SQL> select * from tb_temp01;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> drop table tb_temp01;
Table dropped.
因为drop也属于DDL操作，进行隐式提交，提交后清除数据
但是会话级临时表不可以必须要对表进行截断或者清除数据，再或者退出会话之后才能删除
SQL> select * from tb_temp02;
no rows selected
SQL> insert into tb_temp02 select * from scott.salgrade;
5 rows created.
SQL> drop table tb_temp02;
drop table tb_temp02 *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use
SQL> commit;
Commit complete.
SQL> drop table tb_temp02;
drop table tb_temp02 *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use
SQL> truncate table tb_temp02;
Table truncated.
SQL> delete from tb_temp02;
0 rows deleted.
SQL> drop table tb_temp02;
Table dropped.总之来说临时表删除的时候表内不能有数据
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
