Of Interest
Business Intelligence and Analytics
Oracle Training
|
Oracle
»
PL-SQL
»
DDL
»
Performance
Global Temporary Tables
Global temporary tables in Oracle.
Potential usage: Similar in some ways to Sybase temporary tables,
global temp tables can be used to store
data in a temporary fashion, for use within the current transaction.
Syntax Sample:
create global temporary table zz_test123 (
rec_id number not null,
rec_data number not null
);
Sample session:
SQL>
SQL>
SQL> insert into zz_test123 values (123, 7777) ;
1 row created.
SQL> insert into zz_test123 values (123, 8888 ) ;
1 row created.
SQL>
SQL> select * from zz_test123 ;
REC_ID REC_DATA
---------- ----------
123 7777
123 8888
SQL>
SQL>
SQL> commit ;
Commit complete.
SQL>
SQL> select * from zz_test123 ;
no rows selected
What happened? When you commit, the rows disappear.
Also: If I exit SQL plus .. and go back in, the table is still there!
SQL>
SQL> select * from zz_test123 ;
no rows selected
TABLESPACE details: These tables live in the TEMP tablespace. Look at this curious output.
SQL> select table_name, tablespace_name from user_tables where table_name like 'ZZ%' ;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ZZ_TEST123
Note that there are two options for data persistence:
on commit delete rows and
on commit preserve rows
These are specified at the end of the CREATE command, e.g.
Syntax: create global temporary table zz_test123 (
rec_id number not null,
rec_data number not null
)
on commit {preserve|delete| rows;
With the "preserve" option, data persists until the end of the login session. The table, as you note, continues to exist across sessions.
Global Temporary Tables can also be indexed - BUT, you cannot run DBMS stats on them.
|
|
|
Get the latest Rocket99 news and tech tips via
|
