You can use these to store data temporarily. I had never used them before and decided to try them out. I hit a problem straight away:
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create global temporary table gtt
2 as select * from dba_synonyms
3 where 1 = 2
4 on commit delete rows
5 /
on commit delete rows
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
SQL>
I couldn't find an example in my documentation using CTAS (create table as select) with a global temporary table. I had a look on the Internet and it seems that several people have already had the same problem. The correct syntax is as follows:
SQL> create global temporary table gtt
2 on commit delete rows
3 as select * from dba_synonyms
4 where 1 = 2
5 /
Table created.
SQL> desc gtt
Name Null? Type
-------------------------- -------- ------------------
OWNER NOT NULL VARCHAR2(30)
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
SQL>
As the name suggests, you can use these tables for temporary storage. I'm not sure why you might want to save the contents of dba_synonyms but it's only an example:
SQL> insert into gtt select * from dba_synonyms
2 /
12401 rows created.
SQL> select count(*) from gtt
2 /
COUNT(*)
----------
12401
SQL>
If you specify on commit delete rows when you create the temporary table, the rows disappear when you do a commit, as you might expect:
SQL> commit;
Commit complete.
SQL> select count(*) from gtt
2 /
COUNT(*)
----------
0
SQL>
The same thing happens after an implied commit:
SQL> insert into gtt select * from dba_synonyms
2 /
12401 rows created.
SQL> select count(*) from gtt
2 /
COUNT(*)
----------
12401
SQL> grant select on gtt to system
2 /
Grant succeeded.
SQL> select count(*) from gtt
2 /
COUNT(*)
----------
0
SQL>
To stop this happening, you have to specify on commit preserve rows instead when you create the table:
SQL> drop table gtt
2 /
Table dropped.
SQL> create global temporary table gtt
2 on commit preserve rows
3 as select * from dba_synonyms
4 where 1 = 2
5 /
Table created.
SQL> insert into gtt select * from dba_synonyms
2 /
12401 rows created.
SQL>
Oracle allows you to use commit work instead of commit to keep in line with standard SQL syntax. Both statements work in the same way:
SQL> commit work;
Commit complete.
SQL>
This time the rows remain in the table:
SQL> select count(*) from gtt
2 /
COUNT(*)
----------
12401
SQL>
And they will stay there for the duration of the session (unless you delete them on purpose). So you cannot drop the table at this point:
SQL> drop table gtt
2 /
drop table gtt
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index
on temporary table already in use
SQL>
However, if you end the session by logging into a new one:
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL>
Then log back in as the original user, the table is there but the contents have gone. (It is the table's contents which are temporary, not the table itself.)
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> select count(*) from gtt
2 /
COUNT(*)
----------
0
SQL>
And now you can drop the table:
SQL> drop table gtt
2 /
Table dropped.
SQL>
