Re: Where is TEMP TABLE created?

From: Billy <vslabs_at_onwe.co.za>
Date: 25 Jul 2005 04:45:18 -0700
Message-ID: <1122291918.832607.171170@z14g2000cwz.googlegroups.com>

Ken wrote:
> If I issue CREATE GLOBAL TEMPORARY TABLE, which tablespace will the temp
> table created on? I queried the user_table data dict and the TABLESPACE col
> for the temp table is NULL! So which tablespace is holding the temp table?

Metalink Note:181132.1
Subject: Different Kinds of Temporary Segments

/quote
1. Temporary Tables

---

In addition to permanent tables, you can create temporary tables to hold
session-private data that only exists during a transaction or session. Oracle
can also allocate temporary segments for temporary indexes created on temporary
tables.
The segment for a temporary table is allocated when the first INSERT into that
table is issued. The SEGMENT_TYPE of this segment is TEMPORARY. This segment is allocated in the default temporary tablespace of the user
creating the temporary table. Note that this is the default temporary tablespace
defined in the create/alter user statement that may be of type PERMANENT.
Oracle drops the segment for a transaction-specific temporary table at the end
of the transaction and drops the segment for a session-specific temporary table
at the end of the session.
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.
/unquote

Using temp tables need careful consideration in Oracle. The reasons (resource/contention/lock escalation/etc.) for using such tables in other RDBMS products are not valid in Oracle.

So-called PL/SQL "tables" (correct term is dynamic arrays) in resides in the PGA.

--
Billy

Received on Mon Jul 25 2005 - 06:45:18 CDT