Global temporary tables

— 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s