基于事务和会话的临时表
[oracle@devel1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 20 14:30:08 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn scott/tiger
Connected.
SQL> create global temporary table temp_table_session
2 on commit preserve rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
SQL> create global temporary table temp_table_transaction
2 on commit delete rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
SQL> insert into tmep_table_session select * from scott.emp;
insert into tmep_table_session select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> insert into temp_table_session select * from scott.emp;
12 rows created.
SQL> insert into temp_table_transaction select * from scott.emp;
12 rows created.
SQL> select session_cnt,transaction_cnt from
2 (select count(*) session_cnt from temp_table_session),
3 (select count(*) transaction_cnt from temp_table_transaction);
SESSION_CNT TRANSACTION_CNT
———– —————
12 12
SQL> commit;
Commit complete.
SQL> l
1* commit
SQL> select session_cnt,transaction_cnt from
(select count(*) session_cnt from temp_table_session),
(select count(*) transaction_cnt from temp_table_transaction); 2 3 ;
(select count(*) transaction_cnt from temp_table_transaction);
*
ERROR at line 3:
ORA-00911: invalid character
SQL> select session_cnt,transaction_cnt from
(select count(*) session_cnt from temp_table_session),
(select count(*) transaction_cnt from temp_table_transaction)
/
2 3 4
SESSION_CNT TRANSACTION_CNT
———– —————
12 0
在同一个会话中。向两个基于事物和会话的临时表中插入数据。在没COMMIT前。插入的数据都可见。COMMIT后，基于会话的可见。基于事务的不可见。在新的会话中，两个都看不见。
