Database Reference
In-Depth Information
Temporary Tables
Temporary tables are used to hold intermediate resultsets for the duration of either a transaction or a session. The
data held in a temporary table is only ever visible to the current session—no other session will see any other session's
data, even if the current session
COMMIT
s the data. Multiuser concurrency is not an issue with regard to temporary
tables either, as one session can never block another session by using a temporary table. Even if we “lock” the
temporary table, it will not prevent other sessions from using their temporary table.
■
as we observed in Chapter 9, temporary tables generate significantly less redo than regular tables. however,
since temporary tables generate undo information for the data they contain, they will generate some amount of redo.
UPDATE
s and
DELETE
s will generate the largest amount;
INSERT
s and
SELECT
s the least amount. We also saw in Chapter 9,
starting with Oracle 12
c
, that temporary tables can be configured to generate next to zero redo; this is done by setting the
TEMP_UNDO_ENABLED
parameter to
TRUE
.
Note
Temporary tables will allocate storage from the currently logged-in user's temporary tablespace, or if they are
accessed from a definer rights procedure, the temporary tablespace of the owner of that procedure will be used.
A global temporary table is really just a template for the table itself. The act of creating a temporary table involves
no storage allocation; no
INITIAL
extent is allocated, as it would be for a regular heap organized table (unless the
deferred segment feature is in effect). Rather, at runtime when a session first puts data into the temporary table, a
temporary segment for that session will be created. Since each session gets its own temporary segment (not just an
extent of an existing segment), every user might be allocating space for her temporary table in different tablespaces.
USER1
might have his temporary tablespace set to
TEMP1
, so his temporary tables will be allocated from this space.
USER2
might have
TEMP2
as her temporary tablespace, and her temporary tables will be allocated there.
Oracle's temporary tables are similar to temporary tables in other relational databases, with the main exception
being that they are
statically
defined. You create them once per database, not once per stored procedure in the
database. They always exist—they will be in the data dictionary as objects, but they will always appear empty until
your session puts data into them. The fact that they are statically defined allows you to create views that reference
temporary tables, to create stored procedures that use static SQL to reference them, and so on.
Temporary tables may be
session
based (data survives in the table across
COMMIT
s but not a disconnect/
reconnect). They may also be
transaction
based (data disappears after a
COMMIT
). Here is an example showing the
behavior of both. I used the
SCOTT.EMP
table as a template:
EODA@ORA12CR1> create global temporary table temp_table_session
2 on commit preserve rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
The
ON COMMIT PRESERVE ROWS
clause makes this a session-based temporary table. Rows will stay in this table
until my session disconnects or I physically remove them via a
DELETE
or
TRUNCATE
. Only my session can see these
rows; no other session will ever see my rows, even after I
COMMIT
.
EODA@ORA12CR1> create global temporary table temp_table_transaction
2 on commit delete rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
