A useful feature for any type of programming is the ability to store and use temporary data. Oracle provides us this ability with temporary tables. These temporary tables are created just like any other table (it uses some special modifiers), and the data definition of this table is visible to all sessions, just like regular tables. The temporary aspect of these tables is in regards to the data. The data is temporary and is visible to only that session inserting the data.
Creating a temporary table
The definition of a temporary table persists just like a permanent
table, but contains either session-specific or transaction-specific data.
Both of these types control how temporary you want the data to be.
The session using the temporary table gets bound to the session
with the first insert into the table. This binding goes away, and thus
the data disappears, by issuing a truncate of the table or by ending either
the session or transaction depending on the temporary table type.
Session-specific
Data that's stored in a session-specific temporary table exists
for the duration of the session and is truncated (delete all of the rows)
when the session is terminated. This means that data can be shared between
transactions in a single session. This type of temporary table is useful
for client/server applications that have a persistent connection to the
database. The DDL for creating a session-specific temporary table is presented
here:
CREATE GLOBAL TEMPORARY TABLE search_results (search_id NUMBER, result_key NUMBER) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE search_results (search_id NUMBER, result_key NUMBER) ON COMMIT DELETE ROWS;
Features
of temporary tables
• Data is visible only to the session.
• The table definition is visible to all sessions.
• In rolling back a transaction to a save point, the data will be lost but the table definition persists.
•
You can create indexes on temporary tables. The indexes created are also
temporary, and the data in the index has the same session or transaction
scope as the data in the table.
• You can create views that access both temporary and permanent tables.
• You can create triggers on a temporary table.
• You can use the TRUNCATE command against the temporary table. This will release the binding between the session and the table but won't affect any other sessions that are using the same temporary table.
• The export and import utilities handle the definition of the temporary table, but not the data.
Restrictions
• Temporary tables can't be index organized, partitioned, or clustered.
• You can't specify foreign key constraints.
• Columns can't be defined as either varray or nested tables.
• You can't specify a tablespace in the storage clause. It will always use the temporary tablespace.
• Parallel DML and queries aren't supported.
• A temporary table must be either session- or transaction-specific—it can't be both.
• Backup and recovery of a temporary table's data isn't available.
• Data in a temporary table can't be exported using the Export utility.
Redo
and undo
Putting data in a temporary table is more efficient than placing
this data in a permanent table. This is primarily due to less redo activity
when a session is applying DML to temporary tables. The Oracle8i/9i Concepts
guide puts it like this: "DML statements on temporary tables do not generate
redo logs for the data changes. However, undo logs for the data and redo
logs for the undo logs are generated." Even though this statement is accurate,
it's pretty confusing. To give you an idea of how the database handles
temporary tables, I've elaborated a bit on this statement from the Concepts
guide.
Oracle writes data for temporary tables into temporary segments and thus doesn't require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it's not entirely eliminated because Oracle must log the changes made to these rollback segments. This is what Oracle is referring to when the manual states "redo logs for the undo logs are generated." If this is still confusing, let's just say that log generation should be approximately half of the log generation (or less) for permanent tables.
Example
The scenario for this example is to create a package that will
use a temporary table to store search results. The package is called by
a Web-based application and returns a reference cursor used to retrieve
the results. The Web application must call this package, retrieve the results,
close the cursor, and commit the transaction to delete the rows. Since
this is a Web application, I'll use the transaction-specific temporary
table named search_results that was created previously.
Listing 1 creates a package named pinnacle and a procedure named test that will be used by the Web application. The procedure test inserts some sample data into the temporary table search_results and returns a reference cursor p_output_cur that will be used to fetch these rows from the temporary table.
Listing
1. Create a test package to store
the results in a temporary table.
create or replace package pinnacle is Type output_cur is ref cursor; procedure test (p_output_cur OUT output_cur); end pinnacle; create or replace package body pinnacle is procedure test (p_output_cur OUT output_cur) is p_search_id number; Begin -- Use any type of query here. insert into search_results (search_id, result_key) select 1, 123456 from dual; open p_output_cur for select search_id,result_key from search_results; End Test; end pinnacle;
Listing
2. Test the pinnacle
package using SQL*Plus.
1 SQL> var p_output_cv refcursor; 2 SQL> exec pinnacle.test (:p_output_cv); 3 SQL> print p_output_cv; SEARCH_ID RESULT_KEY ---------- ---------- 1 123456 4 SQL> select * from search_results; SEARCH_ID RESULT_KEY ---------- ---------- 1 123456 5 SQL> commit; 6 SQL> select * from search_results; no rows selected