Re: Where is a Temporary table stored?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 22 May 2003 05:58:46 +1000
Message-ID: <GIQya.39489$1s1.557674@newsfeeds.bigpond.com>

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message news:abjncv07cph3vj2f6kr9qsipvlstp7ilqm_at_4ax.com...
> On Tue, 20 May 2003 16:19:14 -0700, Daniel Morgan
> <damorgan_at_exxesolutions.com> wrote:
>
> >Christian Hartmann wrote:
> >
> >> Hi there,
> >>
> >> I am running Oracle 8.1.7 and wonder myself, where the content of a
> >> temporary table that was created using
> >>
> >> CREATE GLOBAL TEMPORARY TABLE ...
> >> [ON COMMIT DELETE ROWS ]
> >>
> >> is stored.
> >>
> >> Is it stored in the SGA or in the PGA of the user-process, i.e. also
> >> using the temporary tablespace?
> >>
> >> Please let me know if you know it.... :-)
> >>
> >> Regards,
> >>
> >> Christian Hartmann
> >>
> >
> >The TEMP tablespace.
>
> This is only the recommended setting, right?
> Oracle is not forcing you to do it.

Not sure I understand the question, but yes you are forced to house global temporary tables in the temporary tablespace, because it's the one form of the 'create table' statement which cannot take a 'tablespace X' clause.

Look at this:

SQL> create table blah1 (
  2 col1 char(5),
  3 col2 number)
  4 tablespace USERS;

Table created.

SQL> create global temporary table blah2 (   2 col1 char(5),
  3 col2 number)
  4 tablespace USERS;
create global temporary table blah2 (
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

Note the two statements are practically identical, but whereas the normal create table can take a 'tablespace xxxx' clause, doing the same thing for the temporary table is an 'unsupported feature'. So there really is no choice about the location of these things.

Further proof:

SQL> select * from v$sort_usage;

no rows selected

SQL> create global temporary table blah2 (   2 col1 char(5),
  3 col2 number)
  4 ;

Table created.

SQL> select * from v$sort_usage;

no rows selected

V$SORT_USAGE is the view that shows the allocation of extents within temporary tablespace. It usually only contains rows when data is actually being swapped down to such a tablespace, right in the middle of a sort.

What's interesting here is that I'm getting no rows returned, even though I've just created the global temporary table BLAH2, as above. That implies that global temporary tables only modify the data dictionary when they are created (to say that they exist), but unlike any other table you've ever met, the mere act of creating them causes no physical storage to be allocated to them.

Now, try this:

SQL> insert into blah2 values ('aaa',1);

1 row created.

SQL> select * from v$sort_usage;

USERNAME                       USER                           SESSION_

SESSION_NUM SQLADDR
SQLHASH

------------------------------ ------------------------------ -------- -----

------ --------

TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
EXTENTS     BLOCKS

SEGRFNO#

------------------------------- --------- --------- ---------- ---------- --

-------- ----------

SCOTT                          SCOTT                          55210C80
 5 5410A40C 2907312366
TEMP                            TEMPORARY DATA             201        273
   1        256          1

So as soon as rows are inserted into the temporary table (which of course would actually be done in memory, in the buffer cache), *then* Oracle allocates space for the table in the temporary tablespace.

Regards
HJR Received on Wed May 21 2003 - 14:58:46 CDT