— Global temporary table which keeps records till session
create global temporary table temp1( year number ) on commit preserve rows;
SQL> insert into temp1 values ( 11 );
1 row created.
SQL> select * from temp1;
YEAR
———-
11
— reconnect to session and check records
SQL> connect /as sysdba
Connected.
SQL> select * from temp1;
no rows selected
— Global temporary table which keeps records till session does a commit/rollback
Example:
— create global temporary table based on commit
create global temporary table temp2( year number ) on commit delete rows;
SQL> insert into temp2 values ( 12 );
1 row created.
SQL> select * from temp2;
YEAR
———-
12
SQL> commit;
Commit complete.
— shows records disappear after commit/rollback
SQL> select * from temp2;
no rows selected
SQL> insert into temp2 values ( 12 );
1 row created.
SQL> rollback;
Rollback complete.
SQL> select * from temp2;
no rows selected
— By checking the value of DURATION in user_tables one can tell if the table is session based on transaction based, if value is SYS#SESSION then it is session based and if the value is SYS$TRANSACTION then it is transaction based
SQL> select duration from user_tables where table_name = ‘TEMP1’;
DURATION
—————
SYS$SESSION
SQL> select duration from dba_tables where table_name = ‘TEMP2’;
DURATION
—————
SYS$TRANSACTION
You must log in to post a comment.