Global Temporary Tables (Part 1)

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>