Many features have arisen throughout the evolution of the Oracle database. In this article, let’s focus on temporary tables. They were introduced fairly late (Oracle 8i) and are now often considered to be indispensable for DBAs and developers.
It’s worth mentioning up front that the table itself is not temporary, but rather the data within it. The data in such a table is stored only as long as the session or transaction lasts and is private for each session, however the definition is visible to all sessions. Of course, after commit or disconnection, the data is lost but the definition of the table remains (it’s not necessary to perform many ddl operations – especially create table – which is a good practice). Likewise, other structures related to the temporary table like synonyms or views won’t disappear after the end of the transaction or session. Indexes created on a temporary table behave similarly.
Temporary tables have all of the features that ordinary tables have like triggers or the statistics about table access cost, join cardinality, etc. as well as information about rows and blocks. It’s important to point out that temporary tables can’t have foreign keys related to other temporary/permanent tables.
Creating a Temporary Table
The statement to create a global temporary table (GTT) is similar to the definition of an ordinary table with the addition of the keywords GLOBAL TEMPORARY. In the clause ON COMMIT, you specify if a table is bound to a transaction (DELETE ROWS) or to a session (PRESERVE ROWS).
Global Temporary Table transaction-specific
CREATE GLOBAL TEMPORARY TABLE table_name ( column_name column_data_type ... ... ) ON COMMIT DELETE ROWS;
Global Temporary Table session-specific
CREATE GLOBAL TEMPORARY TABLE table_name ( column_name column_data_type ... ... ) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE temp_users ( id number(5) NOT NULL, name varchar2(50) NOT NULL, surname varchar2(50) NOT NULL ) ON COMMIT DELETE ROWS INSERT INTO temp_users VALUES (1, 'John', 'Smith'); INSERT INTO temp_users VALUES (2, 'Anne', 'Parker'); INSERT INTO temp_users VALUES (3, 'Kate', 'Doe'); SELECT COUNT(*) FROM temp_users; -- RESULT: 3 COMMIT; SELECT COUNT(*) FROM temp_users; -- RESULT: 0
How to Save Space Used by Temporary Tables?
Rows associated with created temporary tables are stored in the default temporary tablespace. Starting with Oracle 11g, Oracle introduced the option to specify the temp tablespace for a GTT. Within a TABLESPACE clause you can allocate the temporary tablespace with a specified extent size
CREATE TEMPORARY TABLESPACE tbs2 TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K; CREATE GLOBAL TEMPORARY TABLE temp_users ( id number(5) NOT NULL, name varchar2(50) NOT NULL, surname varchar2(50) NOT NULL) ON COMMIT DELETE ROWS; TABLESPACE tbs2;
Examples of Usage
- they are used for manipulating replicated data from remote tables for some kind of arithmetic
- useful in an application where you need to temporarily store a set of rows to be processed against other tables, for either a session or a transaction
- used to convey data among triggers for either a session or a transaction