The command create global
temporary table creates a temporary table; all users who have permissions on the table itself can
perform DML on a temporary table. However, each user sees their own and only their own data
in the table. When a user truncates a temporary table, only the data that they inserted is removed
from the table. Global temporary tables are useful in situations where a large number of users
need a table to hold temporary data for their session or transaction, while only needing one
definition of the table in the data dictionary. Global temporary tables have the added advantage
of reducing the need for redo or undo space for the entries in the table in a recovery scenario. The
entries in a global temporary table, by their nature, are not permanent and therefore do not need
to be recovered during instance or media recovery.
There are two different flavors of temporary data in a temporary table: temporary for the
duration of the transaction, and temporary for the duration of the session. The longevity of the
temporary data is controlled by the on commit clause; on commit delete rows removes all rows
554 Oracle Database 11g DBA Handbook
from the temporary table when a commit or rollback is issued, and on commit preserve rows
keeps the rows in the table beyond the transaction boundary. However, when the user??™s session
is terminated, all of the user??™s rows in the temporary table are removed.
In the following example, you create a global temporary table to hold some intermediate
totals for the duration of the transaction.
Pages:
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829