global temporary table with on commit preserve rows; Incomplete ?? More...

Detailed Description

global temporary table with on commit preserve rows; Incomplete ??

sqlplus login opens up a connection and session. We can close the session by doing a disconnect;
We can again open the session by doing a connect;
Two sqlplus processes means two connections and two sessions.
If we do disconnect on the two sessions then we will have two connections but no sessions .
The table is visible across the sessions.
Data will not be visible across the sessions.
Because of 'on commit preserve rows', the data will be available even after the transaction is done.

Open two sqlplus sessions.
Run the following in any session to make sure that we start in clean state
truncate table tab_tmp;
drop table tab_tmp;
Run the following in First session
create global temporary table tab_tmp (name VARCHAR2(30)) on commit preserve rows;
Run the following in Second session
select * from tab_tmp;
Second session can see the table
Run the following in First session
insert into tab_tmp values('abc');
Run the following in First session
select * from tab_tmp;First session can see the row
Run the following in Second session
select * from tab_tmp;
Second session can't see the row
Run the following in First session
disconnect;
Run the following in First session
connect;
Run the following in First session
select * from tab_tmp;First session can't see the row
Run the following in First session
show autocommit;autocommit should be off
Run the following in First session
insert into tab_tmp values('abc');
Run the following in First session
commit;
Run the following in First session
select * from tab_tmp;First session can see the row